Introduction: In my previous SQL Server article I described all about Operators in SQL Server. In this article I will explain about most commonly used date and time function of the SQL Server.
The most commonly used DateTime function in SQL Server is listed below:
  • GETDATE()
  • DATEADD()
  • DATEDIFF()
  • DATEPART()
  • DATENAME()
  • DAY()
  • MONTH()
  • YEAR()
GETDATE()
GATEDATE() is very frequently used function. This method returns date and time of the system. This method doesn't accept any parameter.
Example:
  1. Declare @Date datetime
  2. set @Date = (SELECT GETDATE());
  3. Print @Date
Output:
Feb 8 2015 11:16PM
DATEADD()
DATEADD() function is used to add the date-time and subtract the date-time. It returns a new date-time based on added or subtracted interval.
Syntax:
  1. DATEADD(datetimepart, number, date)
here datetimepart parameter tells that which part of the date-time you want to change means increment and decrement. Which may be day, month, second, hours etc.
Example:
  1. --Adding days
  2. Select DATEADD(day, 5,getdate()) as New_Date
  4. --Subtracting days
  5. SELECT DATEADD(day, -2,getdate()) as New_Date
  7. --Adding Months
  8. SELECT DATEADD(MONTH, 2,getdate()) as New_Month
  10. --Subtracting Months
  11. SELECT DATEADD(MONTH, 2,getdate()) as New_Month
Output:
DATEDIFF()
DATEDIFF() function is a very common function which is used to find the difference between two days.
Syntax:
  1. DATEDIFF(datepart, starting_date, ending_date)
Example:
  1. -- Declare Four DateTime Variable
  2. Declare @Starting_Date datetime
  3. Declare @Ending_Date datetime
  4. Declare @Ending_Month datetime
  5. Declare @Ending_Year datetime
  7. -- Set @Staring_Date with Current Date
  8. set @Starting_Date = (SELECT GETDATE());
  10. -- Set @Ending_Date with 5 days more than @Ending_Date
  11. set @Ending_Date = (SELECT DATEADD(day, 5,@Starting_Date ))
  12. -- Get The Date Difference
  13. SELECT DATEDIFF(day, @Starting_Date, @Ending_Date) AS Difference_Of_Days
  15. -- Set @Ending_Date with 8 Month more than @Ending_Date
  16. set @Ending_Month = (SELECT DATEADD(MONTH, 8,@Starting_Date ))
  17. -- Get The Date Difference
  18. SELECT DATEDIFF(MONTH, @Starting_Date, @Ending_Month) AS Difference_Of_Months
  20. -- Set @Ending_Date with 2 Month more than @Ending_Date
  21. set @Ending_Year = (SELECT DATEADD(YEAR, 2,@Starting_Date ))
  22. -- Get The Date Difference
  23. SELECT DATEDIFF(YEAR, @Starting_Date, @Ending_Year) AS Difference_Of_Years
Output:
DATEPART()
When we need a part of the date or time then we use DATEPART() function.
Syntax:
  1. DATEPART(datepart, date)
Example:
  1. declare @date datetime
  2. set @date=GETDATE();
  4. SELECT DATEPART(DAY, @date) AS Day,
  5. DATEPART(MONTH, @date) AS Month,
  6. DATEPART(YEAR, @date) AS Year,
  7. DATEPART(HOUR, @date) AS Hour,
  8. DATEPART(MINUTE,@date) AS Minute,
  9. DATEPART(SECOND, @date) AS SECOND
Output:
DATENAME()
This function is very useful function by using this you can get the name from the datetime value.
Syntax:
  1. DATENAME(datepart, date)
Example:
  1. -- Get Today
  2. SELECT DATENAME(DW, getdate()) AS 'Today Is'
  4. -- Get Month name
  5. SELECT DATENAME(M, getdate()) AS 'Month'
Day()
By using this function you can get the day from any datetime value.
Syntax:
  1. DAY(datetime)
Example:
  1. SELECT DAY(getdate()) 'TODAY DATE'
Output:
MONTH()
By using this function you can get the month from any datetime value.
Syntax:
  1. MONTH(datetime)
Example:
  1. SELECT MONTH(getdate()) 'MONTH'
Output:
YEAR
By using this function you can get the Year from any datetime value.
Syntax:
  1. YEAR(getdate()) 'Year'
Example:
  1. SELECT YEAR(getdate()) 'Year'
Output :