Universal Date Time Formatting with C#
-
I have an application that needs to record data in a database and the particular date when that data was written to the database. More, I need customers to be able to retrieve data from the database by date or date range. To that end, the MS Access database I'm using has a field called TestDate of type Date/Time, and I set up two date/time pickers to allow customers to select a date or date range from which to get data. All this works well and good until I change my regional/language options so the date format changes, say from mm/dd/yyyy to dd/mm/yyyy. Then, although the MS Access database updates the TestDate data automatically to reflect the change in date/time format and my date/time pickers in the C# appilcation reflect the altered format, the result of executing SQL statements based on the date/time pickers does not return the correct data from the database. Rather, selecting all data from December 10, 2007 retrieves data from October 12, 2007. By contrast, if I select, say November 29, 2007, the correct data is returned. Something, then, is getting confused about ambiguous dates. Any suggestions would be appreciated.
-
I have an application that needs to record data in a database and the particular date when that data was written to the database. More, I need customers to be able to retrieve data from the database by date or date range. To that end, the MS Access database I'm using has a field called TestDate of type Date/Time, and I set up two date/time pickers to allow customers to select a date or date range from which to get data. All this works well and good until I change my regional/language options so the date format changes, say from mm/dd/yyyy to dd/mm/yyyy. Then, although the MS Access database updates the TestDate data automatically to reflect the change in date/time format and my date/time pickers in the C# appilcation reflect the altered format, the result of executing SQL statements based on the date/time pickers does not return the correct data from the database. Rather, selecting all data from December 10, 2007 retrieves data from October 12, 2007. By contrast, if I select, say November 29, 2007, the correct data is returned. Something, then, is getting confused about ambiguous dates. Any suggestions would be appreciated.
You are storing using DateTime fields from the textboxes aren't you, rather than using a string? More importantly, you are using a parameter to put the field into the database aren't you? If you want to force a UTC date, then you use the U format qualifier with the DateTime. For example
DateTime dt = DateTime.Now; Console.WriteLine(dt.ToString("U"));Deja View - the feeling that you've seen this post before.
-
You are storing using DateTime fields from the textboxes aren't you, rather than using a string? More importantly, you are using a parameter to put the field into the database aren't you? If you want to force a UTC date, then you use the U format qualifier with the DateTime. For example
DateTime dt = DateTime.Now; Console.WriteLine(dt.ToString("U"));Deja View - the feeling that you've seen this post before.
Yes, I am using a parameterized query to insert new records into the database and storing the data using the DateTime data type rather than a string. Still, somewhere between picking the datetime for the SELECT statement and retrieving the data from the database, the date time format gets lost in translation. If I select a non-ambiguous date such as 13/09/2007 (September 13, 2007), execution of the SQL command returns the correct data, but selecting an ambiguous date, such as 3/12/2007 (intending December 3, 2007) returns erroneous data. The report shows 12/3/2007 (March 12, 2007), but the value of the date prior to the execution of the SQL statement is 3/12/2007 (December 3, 2007).
-
Yes, I am using a parameterized query to insert new records into the database and storing the data using the DateTime data type rather than a string. Still, somewhere between picking the datetime for the SELECT statement and retrieving the data from the database, the date time format gets lost in translation. If I select a non-ambiguous date such as 13/09/2007 (September 13, 2007), execution of the SQL command returns the correct data, but selecting an ambiguous date, such as 3/12/2007 (intending December 3, 2007) returns erroneous data. The report shows 12/3/2007 (March 12, 2007), but the value of the date prior to the execution of the SQL statement is 3/12/2007 (December 3, 2007).
Hi, I'm learning C# by lurking the forums, but have much experience in Access. This solution works in Access, and I think it will work in C# but haven't tested yet. In an Access query you have to enclose the date in # to force american date format. This is how I do this (In Access) Function ScriptaUDF(Datum As Date) ScriptaUDF = "#" & Trim(Str(Month(Datum))) & "/" & Trim(Str(Day(Datum))) & "/" & Trim(Str(Year(Datum))) & "#" End Function So whenever I need a date in a query I use: where (((Table.datefield) = " & ScriptaUDF(Me!Datefield) & ")) I hope this helps
-
Hi, I'm learning C# by lurking the forums, but have much experience in Access. This solution works in Access, and I think it will work in C# but haven't tested yet. In an Access query you have to enclose the date in # to force american date format. This is how I do this (In Access) Function ScriptaUDF(Datum As Date) ScriptaUDF = "#" & Trim(Str(Month(Datum))) & "/" & Trim(Str(Day(Datum))) & "/" & Trim(Str(Year(Datum))) & "#" End Function So whenever I need a date in a query I use: where (((Table.datefield) = " & ScriptaUDF(Me!Datefield) & ")) I hope this helps
That's interesting. The problem I've been having occurs, seemingly, during the execution of the SQL statement. I enclose my SQL SELECT statements with a # sign currently, so "SELECT * FROM Table WHERE Date BETWEEN #" startdate #" AND #" endate" If the # character forces an American date format, that would explain much, I think.
-
Hi, I'm learning C# by lurking the forums, but have much experience in Access. This solution works in Access, and I think it will work in C# but haven't tested yet. In an Access query you have to enclose the date in # to force american date format. This is how I do this (In Access) Function ScriptaUDF(Datum As Date) ScriptaUDF = "#" & Trim(Str(Month(Datum))) & "/" & Trim(Str(Day(Datum))) & "/" & Trim(Str(Year(Datum))) & "#" End Function So whenever I need a date in a query I use: where (((Table.datefield) = " & ScriptaUDF(Me!Datefield) & ")) I hope this helps
How would I specify a date in a SQL expression without forcing the american date format? Is there something I can use to insert a date in the SQL query without using the # sign? Or, if I use the # sign, can I simply force the format that is needed?
-
How would I specify a date in a SQL expression without forcing the american date format? Is there something I can use to insert a date in the SQL query without using the # sign? Or, if I use the # sign, can I simply force the format that is needed?
I found a solution. Basically, the Jet Engine seems to have problems with translation. To get the correct data, I have to do this: SELECT * FROM TABLE WHERE Date BETWEEN Format(#" + startDate + "#,'mm/dd/yyyy')AND Format(#" + endDate + "#,'mm/dd/yyyy') By setting the format in the SQL statement to United States format, the data seems to come back to me correctly.