C＃date问题无法在SQL server2008中插入日期 [英] C#date problem cannot insert date in SQL server2008

问题描述

我正在编写c＃School Programmers Heaven by faraj Rasheed。完成所有编码完全相同，但我在这一行不断收到错误



row [dateOfPublishing] = txtDateOfPublishing.Text

datadapter .Update（ds，article）;



无法更新数据，如果我输入日期为小于12的任何内容，如01/01/2000或03/05 / 2000它工作正常，但当我输入12以上的日期时，它不会像13/05/2001或5/13/2001那样显示错误，如



13/5 / 2001-从角色转换日期和/或时间时转换失败



05/13 / 2001-'String未被识别为有效的DateTime.Couldn 't store< 01-21-2007 00:00:00>在dateOfPublishing列中。预期的类型是DateTime。'



我甚至使用了日期选择器控件但显示相同的错误..很多网上但很不明白怎么做我的系统日期格式为dd / mm / yyyy。我无法根据用户需求更改此格式..



任何人都可以帮忙...



我尝试了什么：



检查所有编码

检查所有转换功能

尝试使用日期时间选择器控件..

也将SQL服务器字段数据类型更改为datetime和datetime2

但没有结果相同的错误

解决方案

不要这样做。

问题是你在SQL中将DATE或DATETIME字段设置为基于文本的日期：所以当你INSERT或UPDATE行时它必须进行转换。到那时，它不知道原始用户在输入日期时使用了什么格式，因此它尝试猜测并使用运行SQL Server的PC的Culture来格式化日期信息。所以它弄错了，你得到一个错误。

而是使用DateTime.TryParse将字符串转换为代码中的DateTime值（使用用户文化来解释它）并插入DateTime值直接进入你的行。这样，传递给SQL的值不需要转换，并且可以正确插入。

看起来你的代码期望日期字符串为MM / DD / YYYY，所以你需要规范化他们。更好的解决方案是使用 DateTime 对象并在尝试将其应用于数据库之前转换字符串。

这是什么意思13/05/2001？您可能会将其解释为2001年第5个月的第13天。我可能会将其解释为2001年第13个月的第5天。其他人可能将其解释为年度第5个月的第2001个月13.该字符串中没有绝对日期信息，因此将其变为日期意味着猜测数字的含义。 SQL猜测你的意思是第13个月的第5天，这是无效的。因此，计算机必须猜测您应该使用计算机不必猜测的格式，或使用默认的明确的SQL格式，而不是使用日期表格。



您需要使用您具有的实际格式（dd / MM / yyyy）的知识将文本转换为DateTime，然后将该DateTime转换为所需格式的字符串

 string d =13/05/2001; 
 DateTime dt; 
 
 if（！DateTime.TryParseExact（d，dd / MM / yyyy，System.Globalization.CultureInfo.CurrentCulture，System.Globalization.DateTimeStyles.None，out dt））
 { 
 //显示字符串格式错误的错误
} 
 
 string sqlDate1 = dt.ToString（dd MMM yyyy）; // 2001年5月13日
 string sqlDate2 = dt.ToString（yyyy-MM-dd）; // 2001-05-13 

sqlDate2是默认的明确的SQL格式，而sqlDate1是一种稍微人性化的日期格式，但是你应该真的使用sqlDate2。



SQL Server明确的日期字符串 - Matt Randle [ ^

i was coding with c# School Programmers Heaven By faraj Rasheed . done all the coding exactly same but i am constantly getting error at this line

row["dateOfPublishing"] = txtDateOfPublishing.Text
datadapter.Update(ds, "article");

unable to update data, if i enter date as anything less than 12 like 01/01/2000 or 03/05/2000 it is working fine but when i enter date above 12 it doesn't takes value like 13/05/2001 or 5/13/2001 Showing error like

13/5/2001- Conversion Failed while converting date and/or time from character

05/13/2001-'String was not recognized as a valid DateTime.Couldn't store <01-21-2007 00:00:00> in dateOfPublishing Column. Expected type is DateTime.'

I have even used date picker control also but same error displays..Seen lot on the web but do not understand how to do it my system date format is dd/mm/yyyy . i cannot change this format as per user demand..

Can anyone help please...

What I have tried:

checked all the coding
checked all the convert function
tried with datetime picker control also..
also changed the SQL server field data type as datetime and datetime2
but no result same error

解决方案

Don't do it like that.
The problem is that you are setting a text based date to a DATE or DATETIME field in SQL: so it has to do the conversion when you INSERT or UPDATE the row. And by then, it has no idea what format the original user used when he entered the date, so it tries to "guess" and uses Culture that the PC that SQL Server is running on to format the date info. So it gets it wrong, and you get an error.
Instead, use DateTime.TryParse to convert the string to a DateTime value in your code (using the user culture to interpret it) and insert the DateTime value into your row directly. That way, the value passed to SQL needs no conversion, and it can be inserted correctly.

It looks like your code is expecting the date strings as MM/DD/YYYY, so you need to normalise them. A better solution is to use a DateTime object and convert the string before trying to apply it to the database.

What does this mean "13/05/2001"? You might interpret that as the 13th day of the 5th month in the year 2001. I might interpret that as the 5th day of the 13th month in the year 2001. Someone else might interpret that as the 2001st day of the 5th month of the year 13. There is no absolute date information in that string so to turn it into a date means guessing at what the numbers mean. SQL is guessing you mean the 5th day of the 13th month and that is invalid. So rather than using date formnats the computer has to guess you should use formats the computer doesn't have to guess, or use the default unambiguous SQL format.

First of all you need to convert your text into a DateTime using the knowledge you have of what format it actually is (dd/MM/yyyy), then convert that DateTime into a string of the desired format

string d = "13/05/2001";
DateTime dt;

if (!DateTime.TryParseExact(d, "dd/MM/yyyy", System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.None, out dt))
{
    // show error that string is in the wrong format
}

string sqlDate1 = dt.ToString("dd MMM yyyy"); // 13 May 2001
string sqlDate2 = dt.ToString("yyyy-MM-dd"); // 2001-05-13

sqlDate2 is the default unambiguous SQL format, whereas sqlDate1 is a slightly more human-friendly date format, but you should really use sqlDate2.

SQL Server unambiguous date strings - Matt Randle[^]

这篇关于C＃date问题无法在SQL server2008中插入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！