Sunday 12 February 2012

DateTime Function in SQL Server


The most commonly used DateTime function in SQL Server are below
  • GETDATE()
  • DATEADD()
  • DATEPART()
  • DATEDIFF()
  • DATENAME()
  • DAY()
  • MONTH()
  • YEAR()
GETDATE() is very common used method which returns exact date time from the system. It does not accept any parameter. Just call it like simple function.
Example :
Declare @Date datetime
set @Date = (SELECT GETDATE());
Print @Date
Result:
Aug 15 2009  9:04PM 
DATEADD()
DATEADD() is used to add or subtract datetime. Its return a new datetime based on the added or subtracted interval.
General Syntax
DATEADD(datepart, number, date)
datepart is the parameter that specifies on which part of the date to return a new value. Number parameter is used to increment datepart.
Example :
Declare @Date datetime
set @Date = (SELECT GETDATE());
print  @Date -- Print Current Date
-- Adding 5 days with Current Date
SELECT DATEADD(day, 5,@Date ) AS NewTime
Result:
Aug 15 2009  9:19PM
NewTime
-----------------------
2009-08-20 21:19:15.170
DATEPART() is used when we need a part of date or time from a datetime variable. We can use DATEPART() method only with select command.
Syntax
DATEPART(datepart, date)
Example :
-- Get Only Year
SELECT DATEPART(year, GETDATE()) AS 'Year'
-- Get Only Month
SELECT DATEPART(month, GETDATE()) AS 'Month'
-- Get Only hour
SELECT DATEPART(hour, GETDATE()) AS 'Hour
Result:
Year
-----------
2009
Month
-----------
8
Hour
-----------
21
DATEDIFF() is very common function to find out the difference between two DateTime elements.
Syntax
DATEDIFF(datepart, startdate, enddate)
Example :
-- Declare Two DateTime Variable
Declare @Date1 datetime
Declare @Date2 datetime
-- Set @Date1 with Current Date
set @Date1 = (SELECT GETDATE());
-- Set @Date2 with 5 days more than @Date1
set @Date2 = (SELECT DATEADD(day, 5,@Date1 ))
-- Get The Date Difference
SELECT DATEDIFF(day, @Date1, @Date2) AS DifferenceOfDay
Result:
DifferenceOfDay
---------------
DATENAME() is very common and most useful function to find out the date name from the datetime value.
Example
-- Get Today
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'
Result:
Today Is
------------------------------
Saturday
Month
------------------------------
August
DAY() is used to get the day from any date time object.
Example:
SELECT DAY(getdate()) AS 'DAY'
Result:
DAY
-----------
15
SELECT MONTH(getdate()) AS 'Month'
Result:
Month
-----------
8
SELECT YEAR(getdate()) AS 'Year'
Result :
Year
-----------
2009

No comments:

Post a Comment