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:
- Declare @Date datetime
- set @Date = (SELECT GETDATE());
- 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:
- 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:
-
- Select DATEADD(day, 5,getdate()) as New_Date
-
-
- SELECT DATEADD(day, -2,getdate()) as New_Date
-
-
- SELECT DATEADD(MONTH, 2,getdate()) as New_Month
-
-
- 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:
- DATEDIFF(datepart, starting_date, ending_date)
Example: -
- Declare @Starting_Date datetime
- Declare @Ending_Date datetime
- Declare @Ending_Month datetime
- Declare @Ending_Year datetime
-
-
- set @Starting_Date = (SELECT GETDATE());
-
-
- set @Ending_Date = (SELECT DATEADD(day, 5,@Starting_Date ))
-
- SELECT DATEDIFF(day, @Starting_Date, @Ending_Date) AS Difference_Of_Days
-
-
- set @Ending_Month = (SELECT DATEADD(MONTH, 8,@Starting_Date ))
-
- SELECT DATEDIFF(MONTH, @Starting_Date, @Ending_Month) AS Difference_Of_Months
-
-
- set @Ending_Year = (SELECT DATEADD(YEAR, 2,@Starting_Date ))
-
- 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:
Example:
- declare @date datetime
- set @date=GETDATE();
-
- SELECT DATEPART(DAY, @date) AS Day,
- DATEPART(MONTH, @date) AS Month,
- DATEPART(YEAR, @date) AS Year,
- DATEPART(HOUR, @date) AS Hour,
- DATEPART(MINUTE,@date) AS Minute,
- 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:
-
- SELECT DATENAME(DW, getdate()) AS 'Today Is'
-
-
- SELECT DATENAME(M, getdate()) AS 'Month'
Day()
By using this function you can get the day from any datetime value.
Syntax:
- SELECT DAY(getdate()) 'TODAY DATE'
Output:
MONTH()
By using this function you can get the month from any datetime value.
Syntax:
Example:
- SELECT MONTH(getdate()) 'MONTH'
Output:
YEARBy using this function you can get the Year from any datetime value.
Syntax:
Example:
- SELECT YEAR(getdate()) 'Year'
Output :