In what format date is stored in SQL server?

Question

In what format date is stored in SQL server?

sanjeev_krs2004

Default port

Points: 1462
More actions
May 5, 2009 at 3:52 am

#131864

Hi All,
I have one situation here where I can't figure out in what format date is stored in SQL 2005 server. I have one application using which I store some data in SQL server. I have installed the application on two different systems. On one of the system, date is stored in dd/mm/yyyy format. While in other, it is being stored as mm/dd/yyyy.
I have checked the regional settings of the systems as well they both are English (United States) and short date format is m/d/yyyy and long date format is dddd, mmmm dd, yyyyy.
I am not sure why date format is changed while storing data. Could you guys help me...?
Regards,
Sanjeev

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

I assume the dates are being stored in DateTime or SmallDateTime data types? If so, the Books Online topic “Date and Time (Transact-SQL)“ describes how SQL Server internally stores dates.

I suspect your problem though is more related to how the dates are being output?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 2

Thanks John,

Could you please explain a bit more when you say - the date is being output...

Regards,

Sanjeev

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi

The client side formatting does not depend on the server configuration (except it is returned as VARCHAR). It depends on the client system. Either the regional settings (you wrote that you already checked). Maybe your applications work with custom culture information or does some own formatting.

Greets

Flo

John H Marsh SSChampion Points: 11671 More actions · Answer 4

Hello Sanjeev,

Where do you see the dates in different formats i.e. is it in a screen displayed by an application? If (in SSMS) you run the same Select statement against the same application tables but on the different Servers, then I would assume that the dates will be displayed in the same format.

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 5

John,

I have checked a report which displays the correct output as I am expecting. But on the other server, it is not. When I drilled down into it, I found that one of the table is not having date in mm/dd/yyyy format. Data is same in both setups so there is no question of having different data so the different output.

On one setup, date is in mm/dd/yyyy format and on other it is dd/mm/yyyy. That's why I am not getting the desired output.

Regards,

Sanjeev

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

SQL Server Books Online (BOL) (in its January 2004 version) states that

Values with the DATETIME data type are stored internally by Microsoft SQL Server as two 4-byte integers.

One integer is the number of days since January 1st of the year 1900.

The other integer is the number of clock-ticks since midnight where a clock-tick is 3.33 milliseconds.

I have installed the application on two different systems. On one of the system, date is stored in dd/mm/yyyy format. While in other, it is being stored as mm/dd/yyyy.

Do you mean that you have installed SQL Server on two different servers and the servers have different regional settings?

Or do you mean that there is one SQL Server and the application is running on two different server and the two application servers have different regional settings?

There is a third possibility that some users are entering dates in mm/dd/yy format and other users are entering dates in dd/mm/yy format, but in both cases, if there is explicit or implicit conversion from a character to a datetime datatype ,the SQL Server date format will be used.

Lastly, it is possible to change the intepretation of a character string of ##/##/## to indicate which of the 3 pieces is the month,day and year.

Run DBCC USEROPTIONS to determine the date format being used by the connection.

SQL = Scarcely Qualifies as a Language

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 7

Hi,

I have installed two different SQL server setups and both servers have same regional settings. And I am facing this issue with data in one particular table only; all other tables are fine.

Entering date manually is not possible coz I am using calender control which is not editable as per my settings.

Intepretation of character string is not possible if user enters a 03/02/2009. Now its pretty hard to differentiate between day and month.

Thanks,

Sanjeev