Date And Time Functions In SQL Server

In this article we learn about Date and time functions in SQL Server.These functions are working on given date and time.These functions are SQL Pre defined functions.

In this article we will  learn about date and time functions in SQL Server.These functions are working on a given date and time.These functions are SQL pre-defined functions.

Types of Datetime Functions

  1. Getdate
  2. Day
  3. Month
  4. Year
  5. Datename
  6. Dateadd
  7. Datediff
  8. Datepart

Getdate()

Getdate function returns current time and date .

Syntax

  1. select Getdate();  

Day()

This function returns a day value from the given date

Syntax

  1. select DAY('2018/10/31')  

Month

This function returns a month value from the given date
 
Syntax
  1. select Month('2018/10/31')  

Year()

This function returns a year value from the given date
 
Syntax
  1. select year('2018/10/31')  

Datename()

It returns the name of the day and month of a given date expression.
 
Example 
  1. select DATENAME(DW,'2018/10/31')  
  2. select DATENAME(MM,'2018/10/31')  
Result
 
Date And Time Functions In SQL Server 
 
Dateadd()
 
This function is used to add a number  of days, months, years to the given date expression.
 
syntax 
  1. select DATEADD('Day/month/year','No. of number want to add','Date')   
Example
  1. select DATEADD(dd,5,getdate())  
  2. select DATEADD(MM,1,getdate())  
  3. select DATEADD(YYYY,1,getdate())   
Result
 
Date And Time Functions In SQL Server 

Datediff() 

Datediff is use to find the difference between dates. It will return the differences between the starting and ending date expressions.
 
Syntax
  1. select datediff('Day/month/year','startDate','endDate')  
Example
  1. select DATEDIFF(dd,'2017/2/3','2018/3/5'as TotalDays  
  2. select DATEDIFF(MM,'2017/2/3','2018/3/5'as TotalMonths  
  3. select DATEDIFF(YYYY,'2017/2/3','2018/3/5'as Totalyear  
  4. select DATEDIFF(WK,'2017/2/3','2018/3/5'as TotalWeeks  
  5. select DATEDIFF(HH,'2017/2/3','2018/3/5'As TotalHours  
  6. select DATEDIFF(MI,'2017/2/3','2018/3/5'As TotalMin   
Result
 
Date And Time Functions In SQL Server 
 
Example 1
 
How we find the difference between row values of a column.
 
Create a Table,
  1. createtable Userdate(ID int identity,birthdate date)  

Add some value in the Table,

  1. insert into Userdate values('1994/12/30')  
  2. insert into Userdate values('1995/7/4')  
  3. insert into Userdate values('2000/9/1')  
  4. insert into Userdate values('1999/12/30')  

Now find the difference. Use this query:

  1. select a1.birthdate ,datediff(day,a1.birthdate,a2.birthdate)as DobDiff from Userdate a1 inner join Userdate a2 on a2.id=a1.id+1  
Result
 
Date And Time Functions In SQL Server 

Datepart()

It returns  day, month, and year values from the given date expression
 
Example
  1. select DATEPART(day,getdate()) as Days  
  2. select DATEPART(MM,getdate()) as Month  
  3. select DATEPART(YYYY,getdate()) as Year  
  4. select DATEPART(WK,getdate()) as Weaks  
  5. select DATEPART(HH,getdate()) as Hours   
Result
 
Date And Time Functions In SQL Server

Summary

In this article we learned datetime functions in SQL Server. Datetime functions are pre-defined functions of SQL.