DATEPART, DATEADD, DATEDIFF SQL Server Standard Datetime Function

Please visit my new Web Site https://coderstechzone.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:

datepart	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Example:
Let we have a table like below:

ID	NAME	DOB
00000001	Shawpnendu Bikash Maloroy	1979-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:

Year	Month	Day	Day of Year
1979	1	1	1

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