Thursday, May 14, 2009

DATEPART, DATEADD, DATEDIFF SQL Server Standard Datetime Function




Please visit my new Web Site WWW.Codedisplay.com



SQL SERVER Transact-SQL provide us a set of functions to retrieve the current date and time or the parts of a DATETIME or SMALLDATETIME value. Say, you can extract the day, month or year from a datetime value, as well as the quarter, week, hour or even the millisecond. In this article, I will describe each of these functions with appropriate syntax and provide the best practical examples like (time difference between two dates or from same date. Day, month, year difference, age calculation) that demonstrate how to use these functions to retrieve datetime data from SQL Server using query. Before start i want to write a simple T-SQL query which return the current datetime based on server clock settings. So to get the server current date time run the below query:

For DATETIME formatting issues you can visit this link.

SELECT GETDATE() [NOW]

SQL Server Date Functions:
1. DATEPART()
2. DATEADD()
3. DATEDIFF()
4. DATENAME()
5. DAY()
6. MONTH()
7. YEAR()
8. CONVERT()
9. CAST()


Use DATEPART():
Returns an integer that represents the specified datepart of the specified date. Such as day, month, year, hour, minute etc.

Signature:
DATEPART(datepart,date)

Where date is a valid date expression and the following table lists all valid datepart arguments:

datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns

Example:
Let we have a table like below:

IDNAMEDOB
00000001Shawpnendu Bikash Maloroy1979-01-01 10:05

Now we want day, month, year & quarter value from DOB column. To achieve this we have to write the following sql by using SQL Server builtin function DATEPART():

SELECT DATEPART(yyyy,DOB) [Year], DATEPART(mm,DOB) [Month], DATEPART(dd,DOB) [Day], DATEPART(dayofyear,DOB) [Day of Year] FROM Developers

And the ouput will be:
YearMonthDayDay of Year
1979111

Use DATEADD():
The DATEADD() function is used to add or subtract a specified time interval from a given date.

Signature:
DATEADD(datepart,number,date)

Where datepart is datepart (see above), number is the interval which specified how many day or month or year you want to add or subtract based on third parameter date.

Example:
Let from today we want to add 65 days or back to 65 days then we can use the below SQL:

SELECT DATEADD(day,65,GETDATE()) [Future], DATEADD(day,-65,GETDATE()) [Past]

Use DATEDIFF():
The DATEDIFF() function is used to returns the time between two given dates.

Signature:
DATEDIFF(datepart,startdate,enddate)

For datepart see above, based on datepart DATEDIFF() function return day or time or quarter between two given date.

Example:
In the below example i will show how you can get hour, day, month, year difference between 2 given dates:

SELECT
DATEDIFF(hh,GETDATE(),GETDATE()+65) [Hour Gap],
DATEDIFF(day,GETDATE(),GETDATE()+65) [Day Gap],
DATEDIFF(month,GETDATE(),GETDATE()+65) [Month Gap],
DATEDIFF(year,GETDATE(),GETDATE()+65) [Year Gap]

Here i want to discuss a bit more on SQL Time Difference because most of the forum i found the question how we can get time difference in SQL for the same date.

Query to get difference in Minute, Query to get difference in Second, Query to get difference in Mili Second:

SELECT DATEDIFF(mi, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') Minute
SELECT DATEDIFF(ss, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') Second
SELECT DATEDIFF(ms, '2009-01-03 22:40:37.00', '2009-01-03 22:40:45.33') MiliSecond

Another interesting example is age calculation. Using DATEDIFF() built in function you can easily calculate personels age up to current date or up to any other date from stored Date of birth data. Here is an example how you can calculate:

-- Change '1979-01-01' by your age column name
-- Change GETDATE() if you don't want to consider system date
SELECT DATEDIFF(year,'1979-01-01',GETDATE()) [Age]

Use DATENAME():
Returns a character string that represents the specified datepart of the specified date. The datepart weekday played an interesting role. By using DATENAME() function you will get all textual week days name like Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday and Friday.

Example:

SELECT DATENAME(weekday, GETDATE())-- RETURN SATURDAY, SUNDAY, MONDAY ...
SELECT DATEPART(weekday, GETDATE())-- RETURN 1, 2, 3.........

Another nice example is to get the textual Month Name from a given date. For diiferent type of Month Name formatting visit my another link which is stated in CONVERT() method section at the bottom of the post.

SELECT DATENAME(month,GETDATE()) [MONTH NAME]
SELECT DATEPART(month,GETDATE()) [MONTH NO]

Use DAY(), MONTH(), YEAR():

DAY() builtin SQL function return the day part from a given date. MONTH() builtin SQL function return no of month from a given date & YEAR() built in SQL function used to get only the year.

Example:

SELECT DAY(GETDATE()) [Day], MONTH(GETDATE()) [Month], YEAR(GETDATE()) [Year]

Use CONVERT():
CONVERT() function will be used to format a date. For more details CLICK HERE.


Use CAST():
CAST() function is used for data type conversion. Lets you want to change data type of string or varchar to another datatype like numeric, int, decimal then CAST() function is the choice. Also you can use CONVERT() function to change the data type.

Syntax:
CAST ( expression AS data_type [ (length ) ])

Example:

SELECT CAST('11.57' AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), '11.57')

Ref:
http://msdn.microsoft.com/en-us/library/ms186724.aspx#DateandTimeFunctions

2 comments:

Joel Theophanes said...

You can't calculate age with just DateDiff since it doesn't take into account months and days. You have to correct the age by comparing the months and subtracting a year if they haven't had their birthday yet by the given date. Also, even if the months are equal, you have to subtract a year if they haven't had their birthday yet that month. Thankfully most people don't track their age to the hour, minute, or second.

Saion Roy said...

Joel thanks to point this issue.
Basically here i think that the practical example for datediff is the age calcualation. Yap you are right. Since you point this issue so that i think i need to give a clear view for next visitors. The logic will be:

1. If birth month>today's month then you have to subtract -1 for year.

2. If both are equal then you have to consider day. If day > today's no of day then also subtract -1

3. Otherwise only datediff is enough.

Hope it will help others.

Thanks for your co-operation.

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash