SQL SERVER 2008 (KATMAI): SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3

SQL Server 2008 introduced a few new data types. One of the new data types added in KATMAI is the DATE data type. The previous versions of SQL Server had two date/time data types DATETIME and SMALLDATETIME. Both of them stored a DATE value and a TIME value together.

Let us have a closer look at these data types.

DATETIME Data Type

DATETIME data type stores a date (day, month and year) along with time (hour, minute, seconds, milliseconds). This data type can store DATE and TIME values between '1753-01-01 00:00:00.000' and '999-12-31 23:59:59.998'. The storage of DATETIME data type takes 8 BYTES.

You might wonder why maximum limit is '999-12-31 23:59:59.998' and not '999-12-31 23:59:59.999'. The following TSQL code will generate an 'out-of-range' error.

DECLARE @d DATETIME
SELECT @d = '9999-12-31 23:59:59.999'

Well, the reason is the following। DATETIME data type is accurate to roughly 3 milliseconds. So '23:59:59.999' rounds to the '00:00:00.000' hour of the next day and that will result in '10000-01-01 00:00:00.000' which is beyond the limit of the valuespace supported by the DATETIME data type.

The following code works.

DECLARE @d DATETIME
SELECT @d = '1753-01-01 00:00:00.000'
SELECT @d = '9999-12-31 23:59:59.998'

We just discussed that the DATETIME data type has an accuracy of roughly 3 milliseconds. Let us look at a few examples that demonstrate it.

DECLARE @d DATETIME
SELECT @d = '01-01-2008 10:00:00.001'
SELECT @d
-- PRINTS '2008-01-01 10:00:00.000'

SELECT @d = '01-01-2008 10:00:00.002'
SELECT @d
-- PRINTS '2008-01-01 10:00:00.003'

SELECT @d = '01-01-2008 10:00:00.003'
SELECT @d
-- PRINTS '2008-01-01 10:00:00.003'

SELECT @d = '01-01-2008 10:00:00.004'
SELECT @d
-- PRINTS '2008-01-01 10:00:00.003'

SELECT @d = '01-01-2008 10:00:00.005'
SELECT @d
-- PRINTS '2008-01-01 10:00:00.007'

SMALLDATETIME Data Type

SMALLDATETIME data type takes 4 BYTES of storage (DATETIME takes 8 BYTES) and can store DATE/TIME values between '1900-01-01 12:00:00' AND '2079-06-06 23:59:00' and are accurate to 1 minute.

DECLARE @d SMALLDATETIME
SELECT @d = '1900-01-01 00:00:00'
SELECT @d = '2079-06-06 23:59:00'

Stripping off the TIME part

DATETIME and SMALLDATETIME data types store values that contain DATE and TIME information. Many times we would come across situations where we need to strip off the TIME part of a DATETIME or SMALLDATETIME value. There are many business requirements where the TIME part of a date column should be ignored.

For example, a sales report for sales between 1st January and 15 January may not consider the TIME part of the InvoiceDate column. In the past, I have seen a few cases where the queries returned incorrect results because of the TIME value in the date columns. For example:

SELECT SUM(InvoiceAmount)
FROM Invoices
WHERE InvoiceDate BETWEEN
 '2008-01-01' AND '2008-01-15'

This query will give incorrect results if the InvoiceDate is stored along with TIME value. In such a case, the above query will exclude all the sales of 15th January because those sales might be made after '2008-01-15 00:00:00'. The correct way to write the above query is:

SELECT SUM(InvoiceAmount)
FROM Invoices
WHERE InvoiceDate
BETWEEN '2008-01-01' AND '2008-01-15 23:59:59'

I have come across many cases where I needed a way to strip off the TIME part from a DATETIME value. There are several ways to do this. My favorite way of doing this is:

-- Initialize a DATETIME variable
DECLARE @d DATETIME
SET @d = '2008-01-15 23:59:59'
SELECT @d -- 2008-01-15 23:59:59.000

-- remove the TIME information
SET @d = DATEADD(d, 0, DATEDIFF(d, 0, @d) )
SELECT @d -- 2008-01-15 00:00:00.000

Another simple method is:

-- Initialize a DATETIME variable
DECLARE @d DATETIME
SET @d = '2008-01-15 23:59:59'
SELECT @d -- 2008-01-15 23:59:59.000

-- remove the TIME information
SET @d = CAST(CONVERT(VARCHAR, @d, 101) AS DATETIME)
SELECT @d -- 2008-01-15 00:00:00.000

A DATETIME value is stored internally as a NUMBER. Hence a numeric operation (First example) will be much more efficient than the second example which uses a CONVERT/CAST operation. There must be many more ways to get this done. However, with the introduction of the DATE data type, SQL Server 2008 will make this task much easier.

DATE Data Type

A DATE data type stores a date value (year, month and day). It is close to what you get after you strip off the TIME portion from a DATETIME value. Using DATE data type, the above conversion can be simplified as:

-- Initialize a DATETIME variable
DECLARE @d DATETIME
SET @d = '2008-01-15 23:59:59'
SELECT @d -- 2008-01-15 23:59:59.000

-- remove the TIME information
SET @d = CAST(@d AS DATE)
SELECT @d -- 2008-01-15 00:00:00.000

DECLARE @d DATE
SET @d = '2008-01-15 23:59:59'
SELECT @d -- 2008-01-15

The DATE data type comes with support for a larger date range. It supports date ranges from '0001-01-01' to '0000-12-31'.

-- support larger range
DECLARE @d DATE
SELECT @d = '0001-01-01'
SELECT @d = '9999-12-31'

String Literal Formats

Literal format ydm is not supported with the DATE data type. All other formats are supported.

DECLARE @d DATE

SET DATEFORMAT mdy
SELECT @d = '01-31-2008'
SELECT @d -- 2008-01-31

SET DATEFORMAT myd
SELECT @d = '01-2008-31'
SELECT @d -- 2008-01-31

SET DATEFORMAT dmy
SELECT @d = '31-01-2008'
SELECT @d -- 2008-01-31

SET DATEFORMAT dym
SELECT @d = '31-2008-01'
SELECT @d -- 2008-01-31

SET DATEFORMAT ymd
SELECT @d = '2008-01-31'
SELECT @d -- 2008-01-31

/*
-- NOT SUPPORTED!!!

SET DATEFORMAT ydm
SELECT @d = '2008-31-01'
SELECT @d -- 2008-01-31
*/

The other DATE/TIME related data types introduced by SQL Server 2008 are TIME, DATETIME2 and DATETIMEOFFSET. We will examine them in the next post.

SQL Server 2008 (KATMAI) TSQL Enhancements

SQL SERVER 2008 (KATMAI)

Saturday, June 7, 2008

SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3 - New Data Type: DATE

0 comments:

Labels

My Blog List

Contact

SQL Server Bloggers

Blog Directories