datetime (SQL)

I often find myself spending brain cycles everytime I have to deal with date and time related operations in SQL. There are a number of types in SQL to support date and time related operations, enough to keep me looking up stack overflow and SQL documentation everytime.

Here are some concepts / examples etc:

[1] Example:

SELECT
CAST(‘2007-05-08 12:35:29. 1234567 +12:15’ AS time(7)) AS T
,CAST(‘2007-05-08 12:35:29. 1234567 +12:15’ AS date) AS D
,CAST(‘2007-05-08 12:35:29.123’ AS smalldatetime) AS SDT
,CAST(‘2007-05-08 12:35:29.123’ AS datetime) AS DT
,CAST(‘2007-05-08 12:35:29. 1234567 +12:15’ AS datetime2(7)) AS DT2
,CAST(‘2007-05-08 12:35:29.1234567 +12:15’ AS datetimeoffset(7)) AS DTO

Output:

datetime1.JPG

[2]  Converting different string formats to datetime

One stop shop here.

[3]  Consider the following table

WITH T1 AS
(
SELECT CAST(‘2007-05-08 12:35:29.123’ AS datetime) AS DT UNION
SELECT CAST(‘2007-05-09 12:35:29.123’ AS datetime) AS DT UNION
SELECT CAST(‘2007-05-10 12:35:29.123’ AS datetime) AS DT UNION
SELECT CAST(‘2007-05-11 12:35:29.123’ AS datetime) AS DT UNION
SELECT CAST(‘MAY 12, 2007′ AS datetime) AS DT UNION
SELECT CAST(’13 MAY 2007’ AS datetime) AS DT UNION
SELECT CONVERT(datetime, ‘20070514’, 112)
)

SELECT *
FROM T1

OUTPUT:

outputQuery1.JPG

SELECT *
FROM T1
WHERE T1.DT > ‘May 9 2007’ AND T1.DT > ‘20070510’ AND T1.DT > ‘2007-05-11’

Note how the strings in the right side of the comparison are being up-casted to datetime under the hood!

OUTPUT:

outputQuery2.JPG

SELECT *
FROM T1
WHERE CAST(T1.DT AS DATE) > CAST(‘May 9 2007’ AS DATE) AND
CAST(T1.DT AS DATE) > CAST(‘20070510’ AS DATE) AND
CAST(T1.DT AS DATE) > CAST(‘2007-05-11’ AS DATE)

Here we are casting to date explicitly. 

OUTPUT:

outputQuery3.JPG

 

[3]  If date is stored as string, and no date / time conversions applied, then the comparison is treated just like a string comparison

WITH T2 AS
(
SELECT ‘2007-05-08’ AS S UNION
SELECT ‘2007-05-09’ AS S UNION
SELECT ‘K’ AS S UNION
SELECT ‘Jan 1 2007 00:00:00’ AS S UNION
SELECT ‘Jan 2 2007’ AS S
)
SELECT *
FROM T2
WHERE T2.S > ‘Jan 1 2007’

OUTPUT : S
Jan 1 2007 00:00:00
Jan 2 2007
K

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