Posts

New Date and Time Data Types

SQL Server 2008 introduces enhancements to the existing date and time data types. The most important changes are the addition of separate DATE and TIME data types. The DATETIME2 data type adds more precision to the existing DATETIME data type, and DATETIMEOFFSET provides support for time zones. Here is a list of the new data types:

New data types:

• DATE (0001-01-01 through 9999-12-31)
• TIME (00:00:00.0000000 through 23:59:59.9999999)
• DATETIME2 (fraction 0 through 7)
• DATETIMEOFFSET (time zone awareness)

There are also a few new functions to support those new data types. Here is a list of the added date and time functions:

New functions:

• SYSDATETIME
• SYSDATETIMEOFFSET
• SYSUTCDATETIME
• SWITCHOFFSET
• TODATETIMEOFFSET

Follows a brief example to illustrate the use of the new date and time data types as well as the new functions.

-- The range for the DATE datatype is from 0001-01-01 through 9999-12-31

DECLARE @date DATE;

 

SET @date = CURRENT_TIMESTAMP;

 

PRINT @date;

 

-- The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999

DECLARE @time TIME;

 

SET @time = CURRENT_TIMESTAMP;

 

PRINT @time;

 

-- Fraction 0 through 7

DECLARE @datetime2 DATETIME2(7);

 

SET @datetime2 = CURRENT_TIMESTAMP;

 

PRINT @datetime2;

 

SET @datetime2 = SYSDATETIME();

 

PRINT @datetime2;

 

-- Timezone offset range: -14:00 through +14:00

-- Date range: 0001-01-01 through 9999-12-31

-- Time range: 00:00:00 through 23:59:59.9999999

DECLARE @datetimeoffset DATETIMEOFFSET(7);

 

SET @datetimeoffset = CURRENT_TIMESTAMP;

 

PRINT @datetimeoffset;

 

SET @datetimeoffset = SYSDATETIMEOFFSET();

 

PRINT @datetimeoffset;

 

-- Functions

SELECT SYSDATETIME(),

       SYSDATETIMEOFFSET(),

       SYSUTCDATETIME(),

       CURRENT_TIMESTAMP,

       GETDATE(),

       GETUTCDATE();

 

-- New ISO week option for week number calculation      

SELECT DATEPART(ISO_WEEK, CURRENT_TIMESTAMP);   

 

-- Switch between time zones

SELECT SYSDATETIMEOFFSET(),

       SWITCHOFFSET(SYSDATETIMEOFFSET(), '+06:00'); --Paris

 

-- Set date and time offset based on DATETIME value

SELECT CURRENT_TIMESTAMP,

       TODATETIMEOFFSET(CURRENT_TIMESTAMP, '-01:00');

 

-- Convert DATETIME to DATE and TIME      

SELECT CONVERT(DATE, CURRENT_TIMESTAMP),

       CONVERT(TIME, CURRENT_TIMESTAMP);

Additional resources:

Using Date and Time Data
http://msdn.microsoft.com/en-us/library/ms180878(SQL.100).aspx

Date and Time Data Types and Functions
http://msdn.microsoft.com/en-us/library/ms186724(SQL.100).aspx

Dates and Date Ranges in SQL Server

One of the most common tasks when working with data is to select data for a specific date range or a date. There are two issues that arise: calculating the date range and trimming the time portion in order to select the full days. Below are a few techniques to show how this can be done.

First, a look at the internals of the DATETIME data type in SQL Server. Its internal representation is as two 4-byte values. The first value represents the number of days since 01/01/1900. The second number is the number of milliseconds since midnight. Here is how those two values can be converted to VARBINARY and then to INT to extract the days and milliseconds portions.

-- The internal representation of a datetime is two 4-byte values

SELECT CURRENT_TIMESTAMP AS 'Today',

       CAST(CURRENT_TIMESTAMP AS VARBINARY(8)) AS 'Two 4-byte Internal',

       SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 1 , 4)

           AS 'Days Since 1900-01-01',

       SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 5 , 4)

           AS 'Milliseconds Since Midnight',

       CAST(SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 1 , 4) AS INT)

           AS 'Days Represented as INT',

       CAST(SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 5 , 4) AS INT)

           AS 'Milliseconds Represented as INT';

Below are a few basic date calculations that show the power of the DATETIME functions and how to calculate key dates that can be used to extract data:

-- Basic dates and date calculations

SELECT CAST('19000101 00:00' AS SMALLDATETIME) AS 'Min SMALLDATETIME',

       CAST('20790606 23:59' AS SMALLDATETIME) AS 'Max SMALLDATETIME',

       CAST('1753-01-01T00:00:00.000' AS DATETIME) AS 'Min DATETIME',

       CAST('9999-12-31T23:59:59.997' AS DATETIME) AS 'Max DATETIME',

       CAST(0 AS DATETIME) AS 'Base SQL Server DATETIME',

       CURRENT_TIMESTAMP AS 'Current Date/Time',

       DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)

           AS 'Current Date/Time as Number (days since 1900-01-01)',

       CAST(DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) AS DATETIME)

           AS 'Today as Date',

       CAST(DATEDIFF(DAY, -1, CURRENT_TIMESTAMP) AS DATETIME)

           AS 'Tomorrow as Date',

       CAST(DATEDIFF(DAY, 1, CURRENT_TIMESTAMP) AS DATETIME)

           AS 'Yesterday as Date',

       DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP)-1, 0)

           AS 'First Day of Last Year',

       DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)

           AS 'First Day of This Year',

       DATEDIFF(DAY, 0, DATEADD(YEAR, -1, CURRENT_TIMESTAMP))

           AS 'Today One Year Ago as Number',

       CAST(DATEDIFF(DAY, 0, DATEADD(YEAR, -1, CURRENT_TIMESTAMP)) AS DATETIME)

           AS 'Today One Year Ago as Date',

       DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

           AS 'First Day of Current Month',

       DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)

           AS 'First Day of Next Month',

       DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)

           AS 'First Day of Prior Month',

       DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0) - 1

           AS 'Last Day of Current Month',

       DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '01:30:00')

           AS '1:30 am today',

       CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 11:30:00.000'

           AS '11:00 am today',

       CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 17:00:00.000'

           AS '5:00 pm today',

       DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0)

           AS 'First Day of This Week',

       DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) - 1, 0)

           AS 'First Day of Last Week',

       DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) + 1, 0)

           AS 'First Day of Next Week';

Let’s implement some date range searches to see this in action. Given table Orders with DATETIME data type column order_date, here are a few range selections:

-- Get Orders for Last Month

SELECT order_date

FROM Orders

WHERE order_date >= DATEADD(MONTH,

                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)

  AND order_date < DATEADD(MONTH,

                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0);

 

-- Get Orders for Current Month

SELECT order_date

FROM Orders

WHERE order_date >= DATEADD(MONTH,

                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

  AND order_date < DATEADD(MONTH,

                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0);

 

-- Get Year to Date Orders

SELECT order_date

FROM Orders

WHERE order_date >= DATEADD(YEAR,

                      DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)

  AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);

 

-- Get Month to Date Orders

SELECT order_date

FROM Orders

WHERE order_date >= DATEADD(MONTH,

                      DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

  AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);

 

-- Get Last Year's Orders

SELECT order_date

FROM Orders

WHERE order_date >= DATEADD(YEAR,

                      DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP)-1, 0)

  AND order_date < DATEADD(YEAR,

                    DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0);

 

-- Get Today's Orders

SELECT order_date

FROM Orders

WHERE order_date >= DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)

  AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);

 

-- Get Yesterday's Orders

SELECT order_date

FROM Orders

WHERE order_date >= DATEDIFF(DAY, 1, CURRENT_TIMESTAMP)

  AND order_date < DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);

 

-- Get Today's Orders Between 9:00 am And 11:00 am

SELECT order_date

FROM Orders

WHERE order_date BETWEEN

           CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 09:00:00.000'

       AND CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 11:00:00.000';

 

-- Or

SELECT order_date

FROM Orders

WHERE order_date BETWEEN

           DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '09:00:00')

       AND DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '11:00:00');

 

-- Get Orders for Last Week

SELECT order_date

FROM Orders

WHERE order_date >= DATEADD(WEEK,

                      DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) - 1, 0)

  AND order_date < DATEADD(WEEK,

                    DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0);

 

-- Get Orders for Current Week

SELECT order_date

FROM Orders

WHERE order_date >= DATEADD(WEEK,

                      DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0)

  AND order_date < DATEADD(WEEK,

                    DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) + 1, 0);

 

-- Get Orders for One Year Back From Current Date

SELECT order_date

FROM Orders

WHERE order_date >= DATEDIFF(DAY, 0,

                      DATEADD(YEAR, -1, CURRENT_TIMESTAMP))

  AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);

There could be many more variations based on the same techniques to calculate dates. All those techniques use the datetime functions DATEADD and DATEDIFF to trim the time portion of the date in order to get accurate results. The key to this is the behavior of DATEDIFF to calculate difference between boundaries (that is when crossing between years, months, days, minutes, etc.). For example, difference in days between May 3, 2006 at 11:59 PM and May 4, 2006 at 00:01 AM returns one day, even it is only a couple minutes. Similar, calculating difference in years between Dec 31, 2006 and Jan 1, 2007 returns one year, even it is only a day or less. With this in mind it is very easy to accomplish the timing effect. Calculating difference one level higher trims the lower level. For example, to trim second calculate difference in minutes, to trim hours (or time in general) calculate difference in days, difference between months will trim days, and so on.

The same trimming can be done by converting to a string and then back to a date. But that is a slower and lengthy way of doing it. Here is a brief example of both methods:

-- Trim time portion

DECLARE @date DATETIME;

SET @date = CURRENT_TIMESTAMP;

 

-- Convertmg to string and back to date

SELECT CONVERT(DATETIME, CONVERT(CHAR(10), @date, 101)) ;

 

-- Using datetime functions

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @date),0);

As a bonus, here is how to convert time stored as a decimal number (like 5.25, 5.50, or 5.75) to a time format (like 5:15, 5:30, or 5:45):

-- Format Time From 5.25 to 5:15

-- Decimal Format to Time Format

DECLARE @time DECIMAL(5, 2);

SET @time = 5.25;

 

SELECT CONVERT(CHAR(5), DATEADD(ss, @time * 3600, 0), 108);

Just for clarification: the CURRENT_TIMESTAMP function returns the current date and time. This function is the ANSI SQL equivalent to the GETDATE function.