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
|
