| Author |
Topic |
|
David Wadsworth
Starting Member
17 Posts |
 Posted - 2011-10-06 : 03:37:32
|
| Hi All,Been on this all night with no resolution.I am using SQL Server 2005 Express.If I INSERT a date in the format yyyy-MM-dd HH:mm:ss it is stored in the database as MM/dd/yyyy HH:mm:ss and not as with my 100 or so other installations dd/MM/yyyy HH:mm:ss.I have found lots of mentions of SET DATEFORMAT but having tried loads of variations (sending query before insert, running on server etc) but this makes no difference.Both server and client machines are set to UK format.One clue may be that this installation of MS SQL 2005 Express was installed while the server was set as US format (the first job I did was to reset this to UK English).I am at the end of my tether, any help would be appreciated.P.S. I immediately thought about re-installing SQL ... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-06 : 03:42:10
|
quote:
If I INSERT a date in the format yyyy-MM-dd HH:mm:ss it is stored in the database as MM/dd/yyyy HH:mm:ss and not as with my 100 or so other installations dd/MM/yyyy HH:mm:ss.
Not really. Date & time is not stored in the database in any specific format like MM/DD/YYYY etc. It is in its internal binary format.quote:
I have found lots of mentions of SET DATEFORMAT but having tried loads of variations (sending query before insert, running on server etc) but this makes no difference.
that is not necessary if you pass in the data in YYYY-MM-DD formatThe main thing is to format the date & time to your required format at the client side when it display the date & time.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
David Wadsworth
Starting Member
17 Posts |
Posted - 2011-10-06 : 03:53:23
|
| Hiya,If I look in the db using Management Studio I see dates stored dd/MM/yyyy HH:mm:ss (before I moved the DB) and after the move they are in MM/dd/yyyy HH:mm:ss. Is this not the problem?I am formatting the responses the same as always. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-06 : 04:00:33
|
quote:
Originally posted by David Wadsworth
Hiya,If I look in the db using Management Studio I see dates stored dd/MM/yyyy HH:mm:ss (before I moved the DB) and after the move they are in MM/dd/yyyy HH:mm:ss. Is this not the problem?I am formatting the responses the same as always.
Not really. That is not how the date is stored in DB (as long as you are using datetime data type). That is how Management Studio present the date to you.http://msdn.microsoft.com/en-us/library/ms187819%28v=SQL.90%29.aspxquote:
Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine 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. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
David Wadsworth
Starting Member
17 Posts |
Posted - 2011-10-06 : 04:23:31
|
| Mate,Thanks for all the help ...How come I can see both formats in the database and when a mixture of old and new are returned to the client the old entries format fine and the new entries format badly.As I said I have up to 200 installations around the world, how is my app gonna detect which is returned to me.I am so confused ...I understand about how the data is stored in the db but how can they show different in Management Studio unless there is some record of which format is used. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-06 : 05:42:52
|
What is the data type of the column ? is it datetime ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
David Wadsworth
Starting Member
17 Posts |
Posted - 2011-10-06 : 06:11:15
|
| Yep, i have several tables containing datetime columns like this and they all do the same ... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-06 : 07:43:28
|
you mean in Management Studio, if you runselect datetime_column from table will see different date format from different SQL Server ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-06 : 07:46:59
|
[code]SELECT @@language, getdate(), convert(varchar(100), getdate())[/code]depending on the language, the result of convert() will be different. But the getdate() should always return in YYYY-MM-DD HH:MM:SS
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
David Wadsworth
Starting Member
17 Posts |
Posted - 2011-10-06 : 10:03:04
|
| Aside from a few seconds difference the result was the same, baffling ... |
|
|
|
David Wadsworth
Starting Member
17 Posts |
Posted - 2011-10-06 : 10:19:26
|
| NEW INFORMATIONI have created a temp table with a datetime column and if I insert a value from within Management Studio the formatting is correct from both servers its just my app.I stopped the app on both servers before they sent data to the server and both are sending correctly formatted data in YYYY-MM-DD (ignoring time part) format. But on the new one it stores and sends back in MM/DD/YYYY format.Arrrrggh! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-06 : 18:44:58
|
how is your App store and read back the date ? Can post the query here ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
David Wadsworth
Starting Member
17 Posts |
Posted - 2011-10-07 : 01:23:31
|
| Hi,"SELECT CreationDate FROM note_table WHERE UIN (Index) = ??"Dim CreateDate as Date = CDate(<data>)lblCreateDate.Text = CreateDate.ToString("dd/MM/yyyy HH:mm:ss")I have instigated a fairly obvious workaround ...Append "sql" to the end of each desktop shortcut and read this on startup as command line args and if found swap format of SQL dates (I use a common date formatter for ALL sql queries and inserts) from YYYY-MM-DD HH:MM:SS to YYYY-DD-MM HH:MM:SS.This is working well but obviously I would prefer to solve the problem as I do not like mods (not the 60's scooter riding hoons).I have only just found out (yeah really) that the box I was connecting to that hosted the new sql server was a virtual XP box running on Linux, so this may have some bearing on the problem.Heartfelt thanks to all contributers ... |
|
|
|
|
Date Format