Understanding Date and Time Functions and Operations in SQL Server
 
Published: 03 Nov 2008
Abstract
Date and Time manipulation is a very common and useful act which deals with database tables and routines. In this article, Abhishek describes the important functions and operations for working with dates and time in SQL Server. The article includes date and time value extraction procedures and search techniques with sample SQL queries, results, and description to give better understanding. He also examines the usage of the SET DATEFORMAT and SET LANGUAGE commands in SQL Server 2005 and also provides a brief overview of the features included with SQL Server 2008 for manipulating Date and Time.
by Abhishek Kumar Singh
Feedback
Average Rating: 
Views (Total / Last 10 Days): 54508/ 137

Introduction

There are about 26 data types defined in Microsoft SQL Server 2005. In general, they are categorized as given in the table below.

Numeric data type

Integers, Decimal, Real, Currency

String

Collection of characters

Temporal (Date/Time)

Dates, Times, or both

Other types

XML, Binary, Image, etc.

As mentioned above - Date/Time or Date data types are also called "Temporal" data type.

Temporal (Date/Time) Date Type

Types of Date/Time data type

1.    datetime

2x4Bytes

January 1,1900 - December 31, 9999

2.    smalldatetime

2x2Bytes

January 1, 1900 - June 6, 2079.

Facts to know about datetime data type

·         It has size of 8 bytes

·         SQL Server does not store datetime value in string format, it stores date time as a number.

·         SQL Server stores number for datetime value which is calculated as the number of days before or after January 1, 1900 and number of milliseconds after midnight of the date.

·         In SQL Query we can operate DATETIME values as string as well as using numbers.

To get the date value by resetting time value to noon time from datetime in SQL Server

·         SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

·         SELECT CONVERT(DATETIME, CONVERT(INT, GETDATE()))

To get only the date part from the datetime in mm/dd/yyyy format string

·         SELECT CONVERT(VARCHAR, GetDate(),101)

To get only the time value of the datetime

·         SELECT GETDATE() - DATEADD(DD,0, DATEDIFF(DD,0, GETDATE())) AS TIME_AT_BASE_DATE

Examples to know the facts about SMALLDATETIME and DATETIME

Query

SELECT CONVERT(SMALLDATETIME,0)

Result

1900-01-01 00:00:00

Reason

Converting an integer value 0 into SMALLDATETIME type results as the base (starting) date

SMALLDATETIME range. It does not fractional part of a second.

Query

SELECT CONVERT(DATETIME,0)

Result

1900-01-01 00:00:00.000

Reason

Converting an integer value 0 into DATETIME type results as the base starting date of DATETIME range. It does show the fractional part of a second too.

Query

SELECT CONVERT(SMALLDATETIME,-1)

Result (Error)

Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type smalldatetime.

Reason

A SMALLDATETIME cannot go back to January 1, 1900.

Query

SELECT CONVERT(DATETIME,-1)

Result

1899-12-31 00:00:00.000

Reason

DATETIME can go back to January 1, 1900. Integer value -1 in DATETIME is equal to 1 day before its base date January 1, 1900 which is December 31, 1899.

Query

SELECT CONVERT(SMALLDATETIME,'2008-09-20 01:26:29.998')

Result

2008-09-20 01:26:00

Reason

A DATETIME value (with fractional part of a second) when converted to a SMALLDATETIME value it rounds off the second value with its fractional part into the minute value. A second value containing its fractional part of 29.998 or less gets rounded down to floor value of minutes. It means that second value 01:26:29.998 = 01:26:00, 01:26:29.997 = 01:26:00 in case of SMALLDATETIME.

Query

SELECT CONVERT(SMALLDATETIME,'2008-09-20 01:26:29.999')

Result

2008-09-20 01:27:00

Reason

A DATETIME value (with fractional part of a second) when converted to a SMALLDATETIME value it rounds off the second value with its fractional part in the minute value. A second value containing its fractional part of 29:999 or greater gets rounded up to ceiling value. It means that second value 01:26:29.999 = 01:27:00, 01:26:30.000 = 01:27:00 in the case of SMALLDATETIME.

Parts of DATETIME in Microsoft SQL Server

SQL Server supports mainly the following list of options for DATEPART function:

                          Datepart

                      Abbreviations

Year

yy, yyyy

Quarter

qq, q

Month

mm, m

Dayofyear

dy, y

Day

dd, d

Week

wk, ww

Weekday

dw

Hour

hh

Minute

mi, n

Second

ss, s

Millisecond

ms

In Microsoft SQL Server the default language is "us-english" which has date format as Year-Month-Date (ymd).

Default base date : Jan 1, 1900

To understand this with examples, execute the following queries. I have added the result and description for each query below.

To convert Numeric value to DateTime

<span class=Bold>Query
 </span>DECLARE @dtDate AS DATETIME
 SET @dtDate = 2;
 SELECT @dtDate AS RESULT

Result
1900-01-03 00:00:00.000

Reason
It adds 2 days to the base date.

Query

SET @dtDate = 2.266;
SELECT @dtDate AS RESULT
Or,
SET @dtDate = CONVERT(DATETIME,2.266);
SELECT @dtDate AS RESULT

Result

1900-01-03 06:23:02.400

Reason
SQL Server adds 2 days in the base date and distributes remaining value 0.266 in hours, minutes, seconds and milliseconds. The entire approach of calculation is given below:

1900-01-01    + 2 days       = 1900-01-03         (Day)

24                * .266           = 6.384                  (Hours)

60                * .384           = 23.04                  (Minutes)

60                * .04            = 2.4                     (Seconds)

1000             * .4              = 400                     (Milliseconds)

Taking all the whole numbers (shown in bold format) the result in date time format becomes:

1900-01-03 06:23:02.400.
System functions for data/time manipulation

ISDATE

Use ISDATE function to check if a date time value is valid.

Example:

SELECT  ISDATE('1753-1-1 23:00:00.000'SELECT  ISDATE('1752-12-31 23:00:00.000')

By using the above mentioned (in the table) datepart options as the parameter of the various SQL Server DATETIME based function, we can get different useful results to be used in our applications. I have described a few of the important functions with examples here.

DATEPART

DATEPART function returns an integer value.

Query

-- To get day number of the year
SELECT DATEPART(dy,'2008-09-20 01:26:29.999'--or
SELECT DATEPART(y,'2008-09-20 01:26:29.999')

Result

264

DATENAME

DATENAME function has similar functionality as DATEPART, but it has one difference in that it returns an output in string format. We can understand it better with the following queries.

1. Query

SELECT DATENAME (mm ,'2008-09-23 09:12:44.310' )      

Result

September

2. Query

SELECT DATENAME (dw ,'2008-09-23 09:12:44.310' )      

Result

Tuesday

DATALENGTH

System function DATALENGTH returns the size in bytes of an expression. Though it is not specific to DATETIME only, I have included it to mention the important fact about dealing with DATETIME in SQL Server. If you try to execute the following queries, you will see the result as given below.

1. Query

SELECT DATALENGTH('2008-09-23 09:12:44.310')

Result

23

2. Query

SELECT DATALENGTH(cast('2008-09-23 09:12:44.310' AS DATETIME))

Result

8

Hence, it clearly means that dealing a DATETIME value with VARCHAR type variables will lead to consuming more memory space. So as far as possible, we should try to use DATETIME datatype and variables for using and manipulating date and times.

You can use DATEADD function to get the DATETIME after adding/subtracting dateparts options like years, months, days, hours, minutes, seconds, milliseconds, etc. You can use the datepart options listed in the above table.

DATEADD

To add days or dateparts in a day we can DATEADD function.

Query

SELECT DATEADD(wk,2,'2008-09-23 09:12:44.310')
 <span class=Bold>Result</span>      
 2008-10-07 09:12:44.310

DATEDIFF

We can use DATEDIFF function to get the difference between two specified dates (with time).

1. Query

SELECT DATEDIFF(day'2008-09-23 09:12:44.310''2008-09-26 19:12:44.310')

Result
3

2. Query

SELECT DATEDIFF(day'2008-09-23 09:12:44.310''2008-09-26 01:12:44.310')

Result

3

If you notice, the actual difference of datetime between the date/time mentioned in the above first query is 3 days and 10 hours. The result is 3 (days). In the second query the actual difference is less than 3 days, still the result is 3 (days). This is because SQL server only checks between the datepart based on the datepart option we mention in the function and ignores other parts of the date while calculating.

So while using DATEDIFF we must use proper datepart to get the correct result. For example, to get actual difference in hours of part of the dates, we can use "hh" in the datepart option of the query. Check the above table for datepart option list.

Query

SELECT DATEDIFF(hh, '2008-09-23 09:12:44.310''2008-09-26 19:10:44.310'

Result
82

Quick examples to understand DATETIME operations

CREATE TABLE ##T(MyDateTime DATETIME)
 
INSERT INTO ##T (MyDateTime) VALUES('2008-09-23 09:12:44.310')

-- Result: 2008-09-23 09:12:44.310 inserted.

INSERT INTO ##T (MyDateTime) VALUES('20080923'

--Must be passed as string. Result 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('23/09/2008'

--Error- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

--The statement has been terminated. In the format ../../.... SQL Server expects first value as the month. Month value 23 is invalid.

INSERT INTO ##T (MyDateTime) VALUES('09/23/2008')

 --Result 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('SEPT 23 2008')

--Error- Conversion failed when converting datetime from character string.

-- Reason- We should either specify three characters for month or full name.

INSERT INTO ##T (MyDateTime) VALUES('SEP 23 2008'

--Result- 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('SEPTEMBER 23 2008'

--Result- 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('SEP 23 08')

 --Result- 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('SEP 23 50')

--Result- 1950-09-23 00:00:00.000 inserted.
--Reason- When we specify only the last two digits of the year, from 00 to 49 will be accepted for the years from 2000 to 2049. I suggest we should always specify four digit years to prevent any unexpected errors. So finally I shall drop the temp table to finish the example.

DROP TABLE ##T
Using SET DATEFORMAT command in SQL Server 2005

By using SET DATEFORMAT <format> through we can set SQL Server to accept a different format of date string to be assigned and used in DATETIME variable.

For example, try to execute the following query:

DECLARE @dt DATETIME
SET @dt = '23/2008/09'
SELECT @dt

In this query we tried to assign a date string of format DYM (date-year-month) into a DATETIME variable, which SQL Server does not accept by default. SQL Server may show the following error. The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

To allow SQL Server to do so, we need to execute the following statement.

SET DATEFORMAT DYM

Now executing this query again will be successful.

Query

DECLARE @dt DATETIME
SET @dt = '23/2008/09'
SELECT @dt

Result

2008-09-23 00:00:00.000

One important point to note is that SET DATEFORMAT <format> command remains valid only for the current connection.

Using SET LANGUAGE command in SQL Server

By applying SET LANGUAGE command you can work with different language supported by Microsoft in the SQL Server. So this provides you a type of globalization feature in SQL Server.

Example:

Query

DECLARE @Today DATETIME
SET @Today = 'SEPTEMBER 23 2008'
 
SET LANGUAGE Italian
SELECT DATENAME(month, @Today) AS 'Month Name'
 
SET LANGUAGE us_english
SELECT DATENAME(month, @Today) AS 'Month Name' 
GO

The result window will be shown as follows:

Changed language setting to Italiano.
Month Name
------------------------------
settembre
 
(1 row(s) affected)
 
Changed language setting to us_english.
Month Name
------------------------------
September
 
(1 row(s) affected)
New date/time features in SQL Server 2008

·         In SQL Server 2008 valid date range has be increased. It can from 0001-01-01 to 9999-01-01.

·         SYSDATETIMEOFFSET function has been added to get the time zone offset with the date-time.

·         In SQL Server 2008 the number of precision for second has been increased up to 7 which is only 3 in the SQL Server 2005 version.

·         SWITCHOFFSET is used to set a new time zone.

·         Also, SQL Server 2008 has added 4 new data types:

* DATE – a date only type
* TIME – a time only type
* DATETIMEOFFSET – a time zone aware datetime type
* DATETIME2 – a datetime type w/ larger fractional seconds and year range than the existing DATETIME type

So maybe in a few years, we will not need to worry so much about separating out dates and times.

Conclusion

When we choose SQL Server database for our applications it is very common to use DATETIME data type in our programs and routines. Hence, for a programmer using SQL Server, it is very important to know how to operate SQL Server's DATETIME feature for specific requirements. I hope this article will be helpful in getting a quick understanding of DATETIME in SQL Server.



User Comments

Title: datetime in sqlserver   
Name: murali
Date: 2010-11-11 12:52:06 AM
Comment:
How to alter the datetime to date conversion
Title: Great!!   
Name: Shyam
Date: 2009-12-30 12:33:12 AM
Comment:
Thank you for your nice article.
it Gives me a better understanding to know time Manipulation Function!
Title: awesome   
Name: mahesh
Date: 2009-05-29 4:15:11 PM
Comment:
great job it helped me a lot thank you very much!!!!!!
Title: Great!!!   
Name: Gourik Kumar Bora
Date: 2009-03-31 3:21:57 AM
Comment:
Hi,
Thanks a lot. quite a helpful article..
Title: set datefirst inside function   
Name: ll
Date: 2008-11-17 7:24:10 AM
Comment:
how to use set datefirst inside function
Title: Thanks.   
Name: Abhishek (Author)
Date: 2008-11-13 8:05:34 AM
Comment:
Thanks to all of you for appreaciating my article.
Title: Thanks a Lot   
Name: Krishna Prabhu
Date: 2008-11-13 4:43:50 AM
Comment:
Thanks a lot for giving this artical.It helped in completing project
Title: Thnx a Ton   
Name: Neha Mishra
Date: 2008-11-12 2:15:52 AM
Comment:
Thnx a ton for such a detailed and informatic article. It helped in clearing almost all my doubts.
Title: Thank you   
Name: Vishal s.surana
Date: 2008-11-11 7:44:52 AM
Comment:
Thank you for your nice article.It is very helpful to understand differnt behaviour of datetime datatype.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 7:36:11 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search