This post follows my blog post Understanding datetime column part I
Date Formats
Unambiguous formats
YYYYMMDD
YYYYMMDD HH:MM:SS
YYYY-MM-DDTHH:MM:SS
Ambiguous formats
DD/MM/YYYY
MM/DD/YYYY
DD-MM-YYYY
MM-DD-YYYY
YYYY-MM-DD
etc
Someone wants to input dates in DD/MM/YYYY format
Declare @test table(dates datetime)
insert into @test
select '12/03/2010'
select dates from @test
Result
dates
---------------------------------
2010-12-03 00:00:00.000
The input value is actually 12th March, 2010, but it is added to the table as December 03, 2010 Let us analyse why this happens
Run the following statement to know the server set options for the current connection
Dbcc useroptions
Result
Set options Value
-----------------------------------
language us_english
dateformat mdy
datefirst 7
.
.
.
.
It returns many rows but I have picked those which are related to datetime From the above settings,
it is clear that Server's date setting is mdy which means it accepts the format as MM/DD/YYYY.
So 12/03/2010 actually treated as December 03, 2010
The format dmy can be expressed as dd/mm/yyy
Format expression
--------------------------------------
dmy dd/mm/yyyy
dd.mm.yyyy
dd-mm-yyyy
mdy mm/dd/yyyy
mm.dd.yyyy
mm-dd-yyyy
ymd yyyy/mm/dd
yyyy.mm.dd
yyyy-mm-dd
myd mm/yyyy/dd
mm.yyyy.dd
mm-yyyy-dd
etc
So, if you want to input dates with particular format, you need to use set dateformat command
set dateformat dmy
Declare @test table(dates datetime)
insert into @test
select '12/03/2010'
union all
select '27/03/2009'
select dates from @test
Result
dates
-----------------------
2010-03-12 00:00:00.000
2009-03-27 00:00:00.000
As you see from the example, set dateformat dmy instructs SQL Server to consider input dates expressed as DD/MM/YYYY (you can use .(dot) or -(hypen) inplace of /(slash) There is another format dd-MMM-yyyy HH:MM:SS which is Unambiguous as long as the default language of the Server is English
Consider the following example which correctly inputs dates although server's date setting is different
Use dmy format
Set dateformat dmy
Declare @test table(dates datetime)
insert into @test
select '12-Mar-2010'
union all
select '27-Mar-2009'
select dates from @test
Result
dates
-----------------------
2010-03-12 00:00:00.000
2009-03-27 00:00:00.000
Use mdy format
Set dateformat mdy
Declare @test table(dates datetime)
insert into @test
select '12-Mar-2010'
union all
select '27-Mar-2009'
select dates from @test
Result
dates
-----------------------
2010-03-12 00:00:00.000
2009-03-27 00:00:00.000
But there is a probelm in dd-MMM-yyyy format as it wont work if language of the Server is not English.
Consider the following example
set language german
Declare @test table(dates datetime)
insert into @test
select '12-Mar-2010'
union all
select '27-Mar-2009'
select dates from @test
Result
Msg 241, Level 16, State 1, Line 2
Fehler beim Konvertieren einer Zeichenfolge in einen datetime-Wert.
It throws a "Error converting a string to a datetime value." error in German language
Another example with YYYY-MM-DD format
set language german
Declare @test table(dates datetime)
insert into @test
select '2010-03-12'
union all
select '2009-03-27'
select dates from @test
Result
Die Spracheneinstellung wurde auf Deutsch geändert.
Msg 242, Level 16, State 3, Line 3
Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs.
Die Anweisung wurde beendet.
It throws an error in German Language
Now use the same code with date values expressed in YYYYMMDD format
set language german
Declare @test table(dates datetime)
insert into @test
select '20100312'
union all
select '20090327'
select dates from @test
Result
dates
-----------------------
2010-03-12 00:00:00.000
2009-03-27 00:00:00.000
Use the same code with date values expressed in YYYY-MM-DDTHH:MM:SS format
set language german
Declare @test table(dates datetime)
insert into @test
select '2010-03-12T00:00:00'
union all
select '2010-03-27T00:00:00'
select dates from @test
Result
dates
-----------------------
2010-03-12 00:00:00.000
2009-03-27 00:00:00.000
From the above examples, it is very clear that only two formats YYYYMMDD and YYYY-MM-DDTHH:MM:SS (Note the Time seperator T between date and time) are Unambiguous formats that can work in all Servers regardless of Dateformat and Language
So you should always express your dates values in either of these formats to give input as datetime columns
Date as a Number
Base date 1900-01-01 00:00:00.000 is equivalent to the number 0
SELECT CAST(0 as DATETIME) as date_time
Result
datetime
--------------------------------------
1900-01-01 00:00:00.000
Remove time part
One of the most wanted requirement is to remove time part from the datetime
There can be many methods to do this
Method 1
(This method is generally used by many people)
declare @date datetime
set @date='2010-01-01 12:45:34.79‘
--Convert to style MM/dd/yyyy
select convert(varchar(10),@date,101) as date_time
Result
date_time
----------------
12/15/2010
Convert back to datetime
Select convert(datetime,convert(varchar(10),@date,101)) as date_time
Result
date_time
--------------------------------
2010-12-15 00:00:00.000
Method 2
Select dateadd(day,datediff(day,0,@date),0) as date_time
Result
date_time
--------------------------------
2010-12-15 00:00:00.000
Find first day of the month
Method 1
(String handling)
declare @date datetime
set @date='2010-03-12 12:36:45'
select cast(cast(year(@date) as char(4))+'-'+right('00'+cast(month(@date) as varchar(2)),2)+'-01' as datetime) as date_time
Result
date_time
-----------------------
2010-03-01 00:00:00.000
Method 2
(Date handling)
select dateadd(month,datediff(month,0,@date),0) as date_time
Result
date_time
-----------------------
2010-03-01 00:00:00.000
Find first day of the Year
Method 1
(String handling)
declare @date datetime
set @date='2010-03-12 12:36:45'
select cast(cast(year(@date) as char(4))+'-01-01' as datetime) as date_time
Result
date_time
-----------------------
2010-03-01 00:00:00.000
Method 2
(Date handling)
select dateadd(year,datediff(year,0,@date),0) as date_time
Result
date_time
-----------------------
2010-03-01 00:00:00.000
Refer Part III that gives more informations on how to use date values in the WHERE clasue
Refer Part IV that gives more informations on the best practises when using date values