Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Date Format

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 format

The 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]

Go to Top of Page

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.
Go to Top of Page

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.aspx
quote:
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]

Go to Top of Page

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.
Go to Top of Page

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]

Go to Top of Page

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 ...
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 07:43:28
you mean in Management Studio, if you run

select datetime_column from table


will see different date format from different SQL Server ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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]

Go to Top of Page

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 ...
Go to Top of Page

David Wadsworth
Starting Member

17 Posts

Posted - 2011-10-06 : 10:19:26
NEW INFORMATION

I 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!
Go to Top of Page

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]

Go to Top of Page

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 ...
Go to Top of Page
   

- Advertisement -