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.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

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 Comment This is for short comments only. Use the forum for more discussions.




HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer