Understanding Date and Time Functions and Operations in SQL Server
page 3 of 8
by Abhishek Kumar Singh
Feedback
Average Rating: 
Views (Total / Last 10 Days): 54504/ 145

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


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-2019 ASPAlliance.com  |  Page Processed at 2019-11-21 5:25:08 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search