Difference in month year and days using DateDiff in MSSQL query

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.ConnectionString = aConnectionString
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
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> "


Response.Write "</table>"

Set rs1 = Nothing

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

Table data is here

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

Be the first to post comment on this article :


Post your comments , suggestion , error , requirements etc here .

We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer