Best Practices – Use Unambiguous Date formats

I see that lot of sql developers do not understand how to effectively express Date/Datetime values in the queries. Many of them tend to use regional date formats.

Let us see what happens when regional date format is used. In India the regional date format is dd/MM/yyyy

declare @date datetime
set @date='10/06/2016'
select @date as date

The result is

date
-----------------------
2016-10-06 00:00:00.000

When you run the following

declare @date datetime
set @date='16/06/2016'
select @date as date

The result is

Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

As you see, the input is given in the form dd/MM/yyyy which means 10th June 2016, but it is actually stored as 6th October,2016. The second code throws an error because 16 is not a valid month. Where is the problem? The problem is using the regional date formats. The default date format is mdy and regional inputs should be expressed in that format only. But if the date format is different, you need to change the expression again.

set dateformat dmy
declare @date datetime
set @date='10/06/2016'
select @date as date

The result is

date
-----------------------
2016-06-10 00:00:00.000

Now it works fine. So based on the server’s date format you need to change the expression. How do we solve this problem? Use Unambiguous date format.

In SQL Server, there are three Unambiguous date formats

YYYYMMDD
YYYYMMDD HH:MM:SS
YYYY-MM-DDTHH:MM:SS

Now run the following and see the result

set dateformat dmy
declare @date datetime
set @date='20160610'
select @date as date
GO
set dateformat mdy
declare @date datetime
set @date='20160610'
select @date as date
GO
set dateformat ymd
declare @date datetime
set @date='20160610'
select @date as date

The result is

date
-----------------------
2016-10-06 00:00:00.000

So it does not matter what the server’s date format is, the unambiguous date formats always work as expected. So you should always express these formats to avoid error/unexpected result

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s