SQL Default Date Format


SQL Tips

Add a Unique Constraint to a Column

CURSOR

DELETE

Dirty Reads and Phantom Reads

INSERT

Recursive SQL

ROW_NUMBER() - Automatic Paging

SELECT, FROM

SQL Default Date Format

UPDATE

WHERE

Sponsored Links

73058_New Scooba® 230 Floor Washing Robot + Free Shipping!

 

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

YYYYMMDD

eg. 20110916


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
SELECT GETDATE()

-- Returns
2011-11-28 22:31:57.953

Now we create some datetimes using the ISO standard for dates, YYYYMMDD.


-- Create some new datetimes
DECLARE @StartTime datetime,
        @EndTime datetime

-- Specify the datetime using the SQL ISO date standard
-- YYYYMMDD
SELECT @StartTime = '20110416'
SELECT @EndTime = '20110616'

-- Display the format
SELECT @StartTime
SELECT @EndTime

-- Returns
2011-04-16 00:00:00.000
2011-06-16 00:00:00.000

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)

-- Returns
61