SQL Default Date Format
Here we look at what the SQL default date format is in Microsoft's SQL Server.
There can be times when in SQL Server you'll be trying to compare some datetime objects, and then all of a sudden as you expand the range of days and SQL Server sends an error message such as:
Msg 242, Level 16, State 3, Line 7
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
This error was caused by having the DateTime object declaring the days and months in the wrong order. Now what I've found is that I can be working on one SQL Server, such as my local SQL Server, and I've got the days and months the right way round. But then when it comes to deploying the code a another SQL Server, such as a staging or production box, I start to get errors like that one just shown.
The reason for this is simple, both SQL Servers have been configured differently, namely their local culture settings, one of them is configured for English English, whilst the other one is configured for American English. Whilst in the UK we have our dates such as DD-MM-YYYY, in the USA days and months are swapped round, such as MM-DD-YYYY. To try and fix this you could try several ways. However, a good solid way is to use the SQL ISO date standard.
SQL ISO date standard
The SQL ISO date standard allows us to specify a datetime in a format which is recognised regardless of the language/culture setting of the SQL Server.
-- SQL Default Date Format
-- is the SQL ISO date standard
You can see official details here: http://support.microsoft.com/kb/173907
SQL Server Default Datetime Examples
Firstly, we call GETDATE() to return the current date and time, and this is returned in the default datetime format configured for the server.
-- Return the current time
Now we create some datetimes using the ISO standard for dates, YYYYMMDD.
-- Create some new datetimes
DECLARE @StartTime datetime,
-- Specify the datetime using the SQL ISO date standard
SELECT @StartTime = '20110416'
SELECT @EndTime = '20110616'
-- Display the format
Finally we use DATEDIFF to see the difference in days between our 2 datetimes we created using the default datetime format for SQL Server.
-- Get the difference between the datetimes by the number of days SELECT DATEDIFF(d, @StartTime, @EndTime)