Most Commonly Used DateTime Function in SQL Server

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  
  3.   
  4. --Subtracting days    
  5. SELECT DATEADD(day, -2,getdate()) as New_Date  
  6.   
  7. --Adding Months  
  8. SELECT DATEADD(MONTH, 2,getdate()) as New_Month  
  9.   
  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   
  6.   
  7. -- Set @Staring_Date with Current Date  
  8. set @Starting_Date = (SELECT GETDATE());  
  9.   
  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  
  14.   
  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  
  19.   
  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();  
  3.   
  4. SELECT DATEPART(DAY, @dateAS Day,   
  5. DATEPART(MONTH, @dateAS Month,   
  6. DATEPART(YEAR, @dateAS Year,   
  7. DATEPART(HOUR, @dateAS Hour,   
  8. DATEPART(MINUTE,@dateAS Minute,   
  9. DATEPART(SECOND, @dateAS 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'  
  3.   
  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 :
 

Up Next
    Ebook Download
    View all
    Learn
    View all
    sourabhsomani.com