February 10, 2006 at 6:41 pm
I've recently upgraded my dbs on to a SQL 2005 server and I have found that the date format when updating a datetime field has reverted to the default LCID of 1033.
I use an LCID of 3081 for all sessions and previously my DB user login was set to British English in EM on the SQL 2000 server.
Therefore if I response.write date() on an asp page I get say 11/02/2006 for 11th Feb. In the db it would be written as: 11/02/2006 7:37:32 AM in my datetime field.
When we upgraded I also set the user login for the db on the SQL 2005 server to British English and now my sessions are correct still showing 11/02/2006 but it is getting written to the db as 2/11/2006 7:37:32 AM.
The application still runs as far as collecting data but now I have a mismatch of dates displayed for historic and post upgrade.
It used to work perfectly on SQL 2000. I've googled but could find no other references to this.
Q. Is there any additional setting required to force the datetime field to store as 11/02/2006 instead of US 1033 format?
Do I need to add anything to my connection string?
I appreciate any help.
Thanks
Stephen
February 13, 2006 at 7:16 am
Hi Stephen,
datetime column data are not stored the way you see them (neither in SQLS 2000 nor 2005). In both cases, a code is stored instead.
From BOL to SQLS 2000:
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
So, part of the problem could be in the way the dates are displayed when you are viewing the tables... but it doesn't look like it, since you say there is a difference between pre- and postupdate dates.
I have little experience with connection strings etc., but generally it is recommended to send dates to SQL Server in a format that is unmistakable and not influenced by any regional settings... for example '20050211' for 11th February - that is YYYYMMDD. If you can manipulate the value before sending it to the server and send it in this format, it should work correctly always, no matter what are settings of the connection, database or server.
HTH, Vladan
February 14, 2006 at 11:44 pm
Thanks Vladan, the field type is DateTime so my date is actually getting written as
11/02/2006 7:37:32 AM (Before upgrade)
2/11/2006 7:37:32 AM. (after upgrade)
I have other date fields which are varchar(25) which hold the 14 digit vbdate 20060211... etc
It's just weird...
February 15, 2006 at 1:45 am
Sorry, I probably didn't make myself clear enough... If your column is Datetime, and you enter the date in format '20060211', it is neither stored nor displayed in this format. It is converted automatically to a correct coded value, which is then displayed in QA as 11/02/2006 00:00:00.000 or 02/11/2006 00:00:00.000 or other, depending on the setting of server, database and connection. Whatever the display is, the code stored is always the same and if you'd see it, you wouldn't probably even realize that it is a date. It is just some number, that is interpreted as a date when displayed or otherwise used in a query.
So, the main thing I'm not sure about is, whether you are just confused by a different display after the upgrade, or whether the problem lies in wrong conversion of input. What does "before upgrade" and "after upgrade" in your post mean? Does it mean
1) When I was viewing any date before upgrade, it was DD/MM/YYYY. Now when I view any date (no matter if entered before or after the upgrade), I see it as MM/DD/YYYY.
2) After the upgrade, when viewing dates entered before the upgrade, they display differently, than dates entered after the upgrade do (i.e., one as DD/MM/YYYY and the other as MM/DD/YYYY).
If 1) is correct, it is just a matter of display and the data are OK.
If 2) is correct, then it seems either before or after the upgrade the dates were entered incorrectly into the system. More probable seems to be that the error started to occur after the upgrade, since otherwise you'd have had conversion errors long ago.
Use the standard interface to enter a date like 16th February 2006. If there is a problem with recognizing month and day, it must throw conversion error (trying to convert using 16 as month). No matter what, check how you are sending the date to SQL Server and make sure it is in unmistakable format, like '20060216'.
February 15, 2006 at 1:56 am
Vladan, here's my db data:
Query: --> SELECT dateCreated,dateCreatedInt FROM Customer WHERE idcust > 145 ORDER BY dateCreatedInt
| dateCreated | dateCreatedInt |
| 16/01/2006 2:59:53 AM | 20060116025953 |
| 26/01/2006 1:36:40 PM | 20060126133640 |
| 29/01/2006 9:05:00 PM | 20060129210500 |
| 2/09/2006 10:42:13 AM | 20060209104213 |
| 2/09/2006 5:35:31 PM | 20060209173531 |
| 2/10/2006 9:51:01 AM | 20060210095101 |
The upgrade occurred first week of February.
Interestingly, on another db I had setup with test data only I saw the same results post upgrade. I truncated the tables today in preparation for live data and the dateCreated field is now storing the data correctly. The dateCreated field is a DateTime type.
February 15, 2006 at 5:05 am
Good, that should help... Please run the same query again and include third column, which will contain CONVERT(varchar(20), dateCreated,113), so that we can see what is really stored in the DB.
So far, everything seems to point in direction of variant 2)... Could you try to run the above query /with added 3rd column/ with a customer that has also some entry with DateCreated greater than 12th February? I'd like to see such table as you posted, which includes "old" dates, date after the upgrade but before 13th and date after 12th February... if possible.
So far it seems the dates after upgrade are incorrectly entered into the system (2/09/2006 5:35:31 PM is in fact 2nd September in the DB).
Vladan
February 15, 2006 at 5:40 am
Ok, this is all the data I can get out of this db:
Query: --> SELECT dateCreated,dateCreatedInt, CONVERT(varchar(20), dateCreated,113) FROM Customer WHERE idcust > 145 ORDER BY dateCreatedInt
| dateCreated | dateCreatedInt | |
| 16/01/2006 2:59:53 AM | 20060116025953 | 16 Jan 2006 02:59:53 |
| 26/01/2006 1:36:40 PM | 20060126133640 | 26 Jan 2006 13:36:40 |
| 29/01/2006 9:05:00 PM | 20060129210500 | 29 Jan 2006 21:05:00 |
| 2/09/2006 10:42:13 AM | 20060209104213 | 02 Sep 2006 10:42:13 |
| 2/09/2006 5:35:31 PM | 20060209173531 | 02 Sep 2006 17:35:31 |
| 2/10/2006 9:51:01 AM | 20060210095101 | 02 Oct 2006 09:51:01 |
And this is from another one of my upgraded dbs:
SELECT dateCreated,dateCreatedInt, CONVERT(varchar(20), dateCreated,113) FROM Customer ORDER BY dateCreatedInt
| dateCreated | dateCreatedInt | |
| 16/12/2005 7:21:18 PM | 20051216192118 | 16 Dec 2005 19:21:18 |
| 19/12/2005 9:00:02 PM | 20051219210002 | 19 Dec 2005 21:00:02 |
| 20/12/2005 5:11:33 PM | 20051220171133 | 20 Dec 2005 17:11:33 |
| 2/07/2006 2:53:22 PM | 20060207145322 | 02 Jul 2006 14:53:22 |
| 13/02/2006 1:18:49 PM | 20060213131849 | 13 Feb 2006 13:18:49 |
| 15/02/2006 1:19:33 PM | 20060215131933 | 15 Feb 2006 13:19:33 |
| 15/02/2006 5:22:44 PM | 20060215172244 | 15 Feb 2006 17:22:44 |
The second one shows the date correct with only one dodgy value in the middle.
February 15, 2006 at 6:25 am
Thanks, that really means the dates after upgrade are entered incorrectly (9th February as 2nd September). As soon as you will try to insert DateCreated with day greater than 12, insert will fail... maybe it is already failing, since you have found no rows with such date.
Things you should do now are:
- modify the code that inserts data into the SQL table, so that you are inserting all dates into Datetime columns in format 'YYYYMMDD HH:MM:SS' (24 hours, no AM/PM) or explicitly converted to datetime type before insert. This will take care of all future inserts.
- correct the errors already stored in the database (all dates after the upgrade are probably wrong); fortunately you have the varchar value of the date stored in another column, so it shouldn't be too hard to do
- check server and DB settings, compare them with the example where it is working fine and find differences
It is extremely hard for me to be more explicit, I don't know anything about your system, what are the requirements and possibilities, how many rows and how many different datetime columns have to be checked, and I may have misunderstood something or taken for granted something that's not true. I can only point out for what you should be looking... hope this will help you at least a bit.
Good luck, Vladan
February 15, 2006 at 7:24 am
Thanks Vladan
I've fixed up the dates in both dbs and in each table that the error occurred. I have put some test orders and customer registrations through and the date format is now correct. I'll keep an eye on it and see how I go.
Thanks for all your help.
Cheers
Stephen
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply