DateDiff in MSSQL query to get difference in days month & year

We can use DateDiff function in MSSQL query to get difference in dates between two date and time fields. Here we will discuss how to use DateDiff inside a MSSQL query.

DateDiff(return type , date1, date2)

Here we can specify the return type like month , days, year, weeks etc. Date1 and date2 are two dates between which the difference is to be calculated.

We will use the same table what we have used in update date and time field example. So let us find out the query required to collect difference in days between two dates.

rs1.open " select book_id,DateDiff(mm,issue_dt,return_dt) as dif, issue_dt, return_dt from dt " , conn

Using the above query we can find out difference in days. The rest of the code remains same for any other record display script. We will display the total script at the end of this tutorial.

Now let us go for finding out difference in years

rs1.open " select book_id,DateDiff(yyyy,issue_dt,return_dt) as dif, issue_dt, return_dt from dt " , conn

You can see the difference in formatting the DateDiff function in above SQL. See the list of different formats for DateDiff here. Here is the total code.

<%
Dim conn,rs,rs1,SQL,RecsAffected,qr,bgcolor

Set conn=Server.CreateObject("ADODB.Connection")
conn.Mode=adModeRead
conn.ConnectionString = aConnectionString
conn.Open
Set rs1 =Server.CreateObject("ADODB.Recordset")

rs1.open " select book_id,DateDiff(yyyy,issue_dt,return_dt) as dif, issue_dt, return_dt from dt " , conn

Response.Write "<table>"

Do While Not rs1.EOF
if(bgcolor="#f1f1f1") then
bgcolor="#ffffff"
Else
bgcolor="#f1f1f1"
End if

Response.Write "<tr bgcolor=" & bgcolor & "><td> " & rs1("book_id") & " </td><td> " & rs1("issue_dt") & " </td><td> " & rs1("return_dt") & " </td><td> " & rs1("dif") & "</td></tr> "

rs1.MoveNext
Loop

Response.Write "</table>"

Set rs1 = Nothing

conn.Close
Set conn = Nothing
%>

Table structure is here

CREATE TABLE [dbo].[dt] (
[book_id] [int] IDENTITY (1, 1) NOT NULL ,
[issue_dt] [datetime] NOT NULL ,
[return_dt] [datetime] NOT NULL
) ON [PRIMARY]
GO

Table data is here

Difference in month year and days using DateDiff in MSSQL query

DateDiff(return type , date1, date2)

rs1.open " select book_id,DateDiff(mm,issue_dt,return_dt) as dif, issue_dt, return_dt from dt " , conn

rs1.open " select book_id,DateDiff(yyyy,issue_dt,return_dt) as dif, issue_dt, return_dt from dt " , conn

CREATE TABLE [dbo].[dt] (
[book_id] [int] IDENTITY (1, 1) NOT NULL ,
[issue_dt] [datetime] NOT NULL ,
[return_dt] [datetime] NOT NULL
) ON [PRIMARY]
GO

3,2007-08-23 00:00:00,2007-09-25 00:00:00
4,2007-12-15 00:00:00,2008-01-02 00:00:00
5,2007-07-05 19:59:56,2007-07-20 19:54:56

Difference in month year and days using DateDiff in MSSQL query

DateDiff(return type , date1, date2)

rs1.open " select book_id,DateDiff(mm,issue_dt,return_dt) as dif, issue_dt, return_dt from dt " , conn

rs1.open " select book_id,DateDiff(yyyy,issue_dt,return_dt) as dif, issue_dt, return_dt from dt " , conn

CREATE TABLE [dbo].[dt] ( [book_id] [int] IDENTITY (1, 1) NOT NULL , [issue_dt] [datetime] NOT NULL , [return_dt] [datetime] NOT NULL ) ON [PRIMARY] GO

3,2007-08-23 00:00:00,2007-09-25 00:00:00 4,2007-12-15 00:00:00,2008-01-02 00:00:00 5,2007-07-05 19:59:56,2007-07-20 19:54:56

CREATE TABLE [dbo].[dt] (
[book_id] [int] IDENTITY (1, 1) NOT NULL ,
[issue_dt] [datetime] NOT NULL ,
[return_dt] [datetime] NOT NULL
) ON [PRIMARY]
GO

3,2007-08-23 00:00:00,2007-09-25 00:00:00
4,2007-12-15 00:00:00,2008-01-02 00:00:00
5,2007-07-05 19:59:56,2007-07-20 19:54:56