August 10, 2004 at 6:13 am
Hi all,
We have just moved a database from a US based server with dates, obviously , in US date format (MM/DD/YYYY). And now have the database sitting on a UK based server.
The question is, will SQL resolve date incompatabilities when querying? Also is it safe to convert all the dates to UK based format without SQL server screwing up?
Thanks
John
August 10, 2004 at 8:02 am
First, SQL Server does not STORE the DATETIME data type in ANY format.
It does store it as an 8 byte value. 4 bytes are the number of days since a default date and the other 4 bytes are the number of 'ticks' (milliseconds) since midnight.
Formatting the DATETIME is only for DISPLAYing the date and time.
So, the dates and times are not stored in your SQL Server in US format. They may display that way based on what the collation is set to, but again that's just how it's displayed.
Refer to the BOL for more information DATETIME data type.
-SQLBill
BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
August 10, 2004 at 8:23 am
Try SET DATEFORMAT command.
August 11, 2004 at 4:46 am
You shouldn't have any problems is you are purely working with datetime, as SQLBill states these are represented the same internally regardless of you regional settings.
However you may experience problems if you query in text or send text dates as parameters as these may have incorrect day and month order. In these cases I use canonical date format, i.e. yyyy-mm-dd [hour:min:sec] which SQL converts correctly regardless of regional settings.
August 16, 2004 at 8:11 am
It depends on how data is being accessed and stored and as far as i know ("i" meaning the most humble, not-worthy me), if the data language is some form of English, the issues are with dates and numerical formats. This question should be addressed to each vendor whose application creates or accesses a database on your SQL Server. Maybe that's you, in which case read on, and I hope it helps.
Some developers practice storing dates in some proprietary string format so that date translation is not an issue, and maybe there's the same practice for numbers. I wouldn't want that burden.
If we could just talk about dates--If the interface updating or querying data in the database uses string dates, or if passes date variables in a program then the SQL server must know the date format of those dates ahead of time. A problem can arise, for example, when a program uses a date function to retrieve the system date, but the Windows Regional Options setting for the date format is in a different date order than what the SQL Server expects.
The date format SQL expects by default is defined by the login language's date format. The login language can be found by examining the properties of a login (windows or sql) in Ent Mgr. It can also be changed this way. Each language has a default for date format. 'English' is MDY, 'British-English' is DMY.
So one way to deal with this is having the ability to set the login id of the user accessing the database, and/or the login language of each database user. You really have to think about this one, though.
Another way is to handle it in the interace by not making dates ambiguous. There is a universal date format that can be passed as a string (as someone mentioned, yyyy-mm-dd hh:mm:ss), or you could use SET DATE FORMAT, as someone else suggested, but that is only in effect during a single connection.
I'm responding to your post because I was looking for other solutions people might have for this. I wish I had known all this before I started my big project 2 years ago. If I had it to do again, I would have planned it so that regardless of what language or regional options' date and numerical formats someone would use, my application would rise above the banter and not mistake July 4th for April 7th.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply