datediff in MS SQL Server

2
20

 

Here datediff is explained with an example

lets create a table my_dates

create table my_dates
(
date1 datetime,
date2 datetime,
)

For testing Insert values with difference 10,100,1000,5 in days from date1 to date2

insert into my_dates select getdate(),getdate()-10
insert into my_dates select getdate(),getdate()-100
insert into my_dates select getdate(),getdate()-1000
insert into my_dates select getdate(),getdate()-5

select *,datediff(day,date2,date1) as day_diff ,datediff(month,date2,date1) as month_diff ,datediff(year,date2,date1) as year_diff from my_dates

date1   date2   day_diff   month_diff   year_diff———————– ———————– ———– ———– ———–
2010-10-12 22:31:20.343   2010-10-02 22:31:20.343 10  0
2010-10-12 22:31:35.153  2010-07-04 22:31:35.153  100 3 0
2010-10-12 22:32:35.013  2008-01-16 22:32:35.013 1000 33 2
2010-10-12 22:33:59.637 2010-10-07 22:33:59.637 5 0 0

(4 row(s) affected)

See the difference note color for corresponding fields

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here