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

select Getdate();

Day()

This function returns a day value from the given date

Syntax

select DAY('2018/10/31')

Month

This function returns a month value from the given date

 

Syntax

select Month('2018/10/31')

Year()

This function returns a year value from the given date

 

Syntax

select year('2018/10/31')

Datename()

It returns the name of the day and month of a given date expression.

 

Example 

select DATENAME(DW,'2018/10/31')
select DATENAME(MM,'2018/10/31')

Result

 

 

 

Dateadd()

 

This function is used to add a number  of days, months, years to the given date expression.

 

syntax 

select DATEADD('Day/month/year','No. of number want to add','Date')

Example

select DATEADD(dd,5,getdate())
select DATEADD(MM,1,getdate())
select DATEADD(YYYY,1,getdate())

Result

 

 

Datediff() 

Datediff is use to find the difference between dates. It will return the differences between the starting and ending date expressions.

 

Syntax

select datediff('Day/month/year','startDate','endDate')

Example

select DATEDIFF(dd,'2017/2/3','2018/3/5') as TotalDays
select DATEDIFF(MM,'2017/2/3','2018/3/5') as TotalMonths
select DATEDIFF(YYYY,'2017/2/3','2018/3/5') as Totalyear
select DATEDIFF(WK,'2017/2/3','2018/3/5') as TotalWeeks
select DATEDIFF(HH,'2017/2/3','2018/3/5') As TotalHours
select DATEDIFF(MI,'2017/2/3','2018/3/5') As TotalMin

Result

 

 

 

Example 1

 

How we find the difference between row values of a column.

 

Create a Table,

createtable Userdate(ID int identity,birthdate date)

Add some value in the Table,

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

Now find the difference. Use this query:

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 returns  day, month, and year values from the given date expression

 

Example

select DATEPART(day,getdate()) as Days
select DATEPART(MM,getdate()) as Month
select DATEPART(YYYY,getdate()) as Year
select DATEPART(WK,getdate()) as Weaks
select DATEPART(HH,getdate()) as Hours

Result

 

Summary

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