A Date with Dates

Posted on January 20, 2008

0


There are some interesting changes in the upcoming release of SQL Server 2008, some of which are powerful and sexy (“Spatial data” anyone?) and some have a less of a wow factor but never less are extremely useful. As a start, I wanted to write a special introductory post about the new date and time datatypes.

In the currnet (2005) and previous versions of Microsoft’s SQL Server there is no date specific datatype to store only the date element. You must use the DATETIME or SMALLDATETIME types. Working with the data sometimes became a chore, especially but not exclusivily when designing reporting solutions. We had to implement CAST and or CONVERT hacks to get around this. How many of us have had to write something along the lines of:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
CAST(CAST(FLOOR(DateEntered) AS FLOAT) AS DATETIME) >= @StartDate
AND < CAST(CAST(FLOOR(DateEntered) AS FLOAT) AS DATETIME) <@EndDate

Well, not anymore. Assuming of course you don’t want the time element of a date, it is now possible to use the new DATE datatype. The following example will output the date without the time element:

DECLARE @MyDt AS DATE
SET
@MyDt = GETDATE()
PRINT
@MyDt

The previous query can be simplified like this:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
DateEntered >= @StartDate
AND < DateEntered <@EndDate

Also , if you still need the granularity of the time the query is still a lot simpler than before:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
CAST(DateEntered AS DATE) >= @StartDate
AND < CAST(DateEntered AS DATE) <@EndDate

The range of this new DATE datatype is from 0001-01-01 through to 9999-12-31. The TIME datatype works in much the same way, it stores only the time element with a range of 00:00:00.0000000 through to 23:59:59.9999999 – providing a very useful type for certain applications.

Lastly, there is an unusual hybrid datatype called DATATIME2. Ok, not the greatest named datatype in the history of naming but it is very interesting. What this allows us to do is store a DATETIME with fractional granuluarity. For instance , do you want to store the date/time without the milliseconds? No problem for Mr DateTime2:

 

DECLARE @MyDt DATETIME2(0)
SET
@MyDt = GETDATE()
PRINT
@MyDt

These are the other available fractional settings you can use:

 

Fraction

Output

0

2007-10-28 22:11:20

1

2007-10-28 22:11:19.7

2

2007-10-28 22:11:19.70

3

2007-10-28 22:11:19.703

4

2007-10-28 22:11:19.7030

5

2007-10-28 22:11:19.70300

6

2007-10-28 22:11:19.703000

7

2007-10-28 22:11:19.7030000

 

I will be delving deeper into the datatypes in future blog posts.

 

 

 

 

Advertisements
Posted in: Datatypes, Katmai, SQL