SQL Default Date Format


InterServer Web Hosting and VPS
Sony Playstation PS4 Pro Custom Build NVMe SSD Drive - Enclosure case and Crucial NVMe SSD
PS4 Pro Custom External SSD NVMe Drive

How I built a PS4 Pro Custom External SSD NVMe Drive...

NUC8i7BEH with memory
Intel NUC8I7BEH Review and Custom Build

This is my review of the Intel NUC8i7BEH and a summary of my build...

Card image cap
Asus M5A88-M EVO Review

This is my review of the Asus M5A88-M EVO motherboard...

Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit
Corsair Vengeance Blue Review

This is my review of the Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit memory...

Robotic Arm with USB PC Interface Review
Robotic Arm with USB PC Interface Review

This is my review of the Robotic Arm with USB PC Interface...

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

Sponsored Links
SolarSystem - AngularJS Demo
SolarSystem - Angular JS Demo

Check out my first Angular JS demo hosted in Azure, it helped win my first Angular contract. Put the sound on.