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.

Types of Datetime Functions

1. Getdate

2. Day

3. Month

4. Year

5.Datename

6.Dateadd

7.Datediff

8.Datepart

Getdate():Getdate function return current time and date .

Syntax:

  1. select Getdate();

Day(): This function is return day value from the given date

Syntax:

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

Month:This function is return Month value from the given date

Syntax:
  1. select Month('2018/10/31')

Year():this function is return year value from the given date

Syntax:
  1. select year('2018/10/31')

Datename():It return the name of the day and month of give date expression.

Example:
  1. select DATENAME(DW,'2018/10/31')
  2. select DATENAME(MM,'2018/10/31')
Result:
Dateadd():This function is used to add no 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

Datediff():Datediff is use to find difference between dates.it will return the differences between 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
  3. select DATEDIFF(MM,'2017/2/3','2018/3/5') as TotalMonths
  5. select DATEDIFF(YYYY,'2017/2/3','2018/3/5') as Totalyear
  7. select DATEDIFF(WK,'2017/2/3','2018/3/5') as TotalWeeks
  9. select DATEDIFF(HH,'2017/2/3','2018/3/5') As TotalHours
  11. select DATEDIFF(MI,'2017/2/3','2018/3/5') As TotalMin
Result:
Example 1: How we find diff Between Row values of a column

create a Table

  1. create table 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

Datepart():It return day,month and year values from the given date expression

Example:
  1. select DATEPART(day,getdate()) as Days
  3. select DATEPART(MM,getdate()) as Month
  5. select DATEPART(YYYY,getdate()) as Year
  7. select DATEPART(WK,getdate()) as Weaks
  9. select DATEPART(HH,getdate()) as Hours
Result:

Summary:In this article we Learned datetime functions in SQL Server.Datetime functions are Pre Defined function of SQL.