Understanding Date and Time Functions and Operations in SQL Server
page 1 of 8
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): 55207/ 107

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.


View Entire Article

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-2017 ASPAlliance.com  |  Page Processed at 2017-02-20 1:15:22 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search