Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Understanding Datetime column - Part II

Jun 3 2010 9:02AM by Madhivanan   


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

Tags: datetime, tsql, BRH, time, date, DataTypes, SQL Server, #SQLServer,


Madhivanan
3 · 40% · 12958
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Very informative topic, thank you for sharing.

    commented on Oct 17 2011 3:57PM
    pammy107
    1296 · 0% · 19
  • Thanks pammy107 for your feedback

    commented on Sep 14 2012 6:33AM
    Madhivanan
    3 · 40% · 12958

Your Comment


Sign Up or Login to post a comment.

"Understanding Datetime column - Part II" rated 5 out of 5 by 4 readers
Understanding Datetime column - Part II , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]