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.