| |
Welcome to the dBforums forums.
You are currently viewing our boards as a guest which gives you limited access to view most discussions, articles and access our other FREE features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload your own photos and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!
If you have any problems with the registration process or your account login, please contact contact support.
If you prefer not to see double-underlined words and corresponding ads, place your cursor here for ContentLink opt out.
|
 |
|
10-08-04, 12:27
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Edinburgh Scotland
Posts: 23
|
|
|
Datediff in ASP/Ultradev
|
I have some code that returns the difference in days between 2 dates
<%=Datediff("d", fromdate, nowdate) %>
This works and the data returned in the browser is correct.
If I add >=28 as below
<%=Datediff("d", fromdate, nowdate) >=28 %>
In the browser I see true or false instead of the number of days. This is not useful as I still want to return the number of days but only if they exceed 28.
Can anybody tell me what I'm doing wrong or offer some assistance so that I can correct this.
Many thanks in advance.
Dave
|
|
10-10-04, 20:38
|
|
Coffee Minion
|
|
Join Date: Nov 2003
Location: Sydney
Posts: 1,515
|
|
nothing like doing things the easy way....
Code:
<%if Datediff("d", fromdate, nowdate) >=28 then response.write(Datediff("d", fromdate, nowdate)) %>
|
|
10-11-04, 09:30
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Edinburgh Scotland
Posts: 23
|
|
|
Thats a great help and has worked for me. Many thanks.
Now I see no of days greater than 28. Presents another problem which should keep me busy.
I need to hide all rows where no data is returned in no of days.
Thanks again.
Dave
|
|
10-11-04, 20:12
|
|
Coffee Minion
|
|
Join Date: Nov 2003
Location: Sydney
Posts: 1,515
|
|
happy to help, if you need assistance with your other problem just yell.
|
|
10-12-04, 05:18
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Edinburgh Scotland
Posts: 23
|
|
I'm yelling.
Spent nearly all day on this. I guess I need to use IF and THEN, but not sure how to impliment this as the rows I want to hide are where the days are less than 28 and no data is returned. Difficult as this column does not form part of the recordest.
If you can assist it would be much appreciated.
Thanks again,
Dave
|
|
10-12-04, 20:15
|
|
Coffee Minion
|
|
Join Date: Nov 2003
Location: Sydney
Posts: 1,515
|
|
How do you determine that no data is returned??
What are the determining factors? Is it if the recordset is empty? If so you would use
Code:
if myRecordset.eof then
myRecordset.eof (end of file) will be true if there are no records or if you have walked through the recordset to the end.
if you want to combine this with the 28 days issue....
Code:
if (Datediff("d", fromdate, nowdate) >=28 ) and not(myRecordset.eof) then
' do xyz
end if
xyz will only be executed if the date difference is great then 28 days and there are no records...
|
|
10-14-04, 13:35
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Edinburgh Scotland
Posts: 23
|
|
Thanks for your help.
I'm not sure if I explained myself very well.
The DateDiff work just fine. The days are counted and displayed only if they match or exceed 28 days. If the number of days is less than 28 then the number of days is not displayed.
To keep this tidy I would want to hide all rows where the number of days is less than 28.
Sorry to keep bothering you, but if you can offer further help I would greatly appreciate it.
Many thanks,
Dave
|
|
10-14-04, 20:53
|
|
Coffee Minion
|
|
Join Date: Nov 2003
Location: Sydney
Posts: 1,515
|
|
still not sure that I really understand but.... I assume you currently have something like
Code:
<tr><td><%if Datediff("d", fromdate, nowdate) >=28 then response.write(Datediff("d", fromdate, nowdate)) %></td></tr>
and that is the row bit you are talking about hiding.... if so, try this instead...
Code:
<%if Datediff("d", fromdate, nowdate) >=28 then response.write("<tr><td>" & Datediff("d", fromdate, nowdate)) & "</td></tr>"%>
|
|
10-17-04, 09:51
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Edinburgh Scotland
Posts: 23
|
|
At present the code is
<% if DateDiff("d", fromdate, nowdate) >=28 then response.write ( Datediff ("d", fromdate, nowdate)) %>
This gives me a table:
Staff No | Name | Team | Sick Date | No of Days
****** | **** | **** | 15/09/02 | 763
****** | **** | **** | 11/02/04 | 614
****** | **** | **** | 06/10/04 |
No of Days for Sick Date 06/10/04 shows no data as the no of sick days is less than 28. If the no of sick days is less than 28, I want to hide the row.
I have played with your extra bit of code, but it does not hide the row.
Many thanks for your help though, it has been very much appreciated.
Dave
|
Last edited by daveyy : 10-17-04 at 10:00.
|
10-17-04, 20:02
|
|
Coffee Minion
|
|
Join Date: Nov 2003
Location: Sydney
Posts: 1,515
|
|
Okie, then at that start of your recordset loop you need to do you check to see if the number of days is > 28 and if it is create a row and if it is not skip to the next record.
If the code you have given me I can't show you exactly how to do this because it does not contain the entire loop. If you attach allt he code I can give you a working example.
|
|
10-18-04, 15:23
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Edinburgh Scotland
Posts: 23
|
|
Many thanks for looking at this. I have attached the code for the page below, hopefully this will help.
Code:
<%@LANGUAGE="VBSCRIPT"%> <%
set rsLTSick = Server.CreateObject("ADODB.Recordset")
rsLTSick.ActiveConnection = "dsn=misp;uid=system;pwd=manager;"
rsLTSick.Source = "SELECT a.staff_no, a.from_date, (p.first_name || ' ' || p.surname), p.team_ref FROM absent a, pdb p WHERE a.to_date is null and a.staff_no = p.staff_no and p.leaving_date is null ORDER BY 2"
rsLTSick.CursorType = 0
rsLTSick.CursorLocation = 2
rsLTSick.LockType = 3
rsLTSick.Open
rsLTSick_numRows = 0
%><%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsLTSick_numRows = rsLTSick_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
' set the record count
rsLTSick_total = rsLTSick.RecordCount
' set the number of rows displayed on this page
If (rsLTSick_numRows < 0) Then
rsLTSick_numRows = rsLTSick_total
Elseif (rsLTSick_numRows = 0) Then
rsLTSick_numRows = 1
End If
' set the first and last displayed record
rsLTSick_first = 1
rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
' if we have the correct record count, check the other stats
If (rsLTSick_total <> -1) Then
If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
If (rsLTSick_numRows > rsLTSick_total) Then rsLTSick_numRows = rsLTSick_total
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them
If (rsLTSick_total = -1) Then
' count the total records by iterating through the recordset
rsLTSick_total=0
While (Not rsLTSick.EOF)
rsLTSick_total = rsLTSick_total + 1
rsLTSick.MoveNext
Wend
' reset the cursor to the beginning
If (rsLTSick.CursorType > 0) Then
rsLTSick.MoveFirst
Else
rsLTSick.Requery
End If
' set the number of rows displayed on this page
If (rsLTSick_numRows < 0 Or rsLTSick_numRows > rsLTSick_total) Then
rsLTSick_numRows = rsLTSick_total
End If
' set the first and last displayed record
rsLTSick_first = 1
rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
End If
%>
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
function DoDateTime(str, nNamedFormat, nLCID)
dim strRet
dim nOldLCID
strRet = str
If (nLCID > -1) Then
oldLCID = Session.LCID
End If
On Error Resume Next
If (nLCID > -1) Then
Session.LCID = nLCID
End If
If ((nLCID < 0) Or (Session.LCID = nLCID)) Then
strRet = FormatDateTime(str, nNamedFormat)
End If
If (nLCID > -1) Then
Session.LCID = oldLCID
End If
DoDateTime = strRet
End Function
</SCRIPT>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">There are <font color="#FF0033"><%=(rsLTSick_total)%></font>
staff with open sick records</font></b></p>
<p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff below
w1th no of sick days have exceeded 28 days sickness and are now Long Term Sick.</font></b>
</p>
<% If Not rsLTSick.EOF Or Not rsLTSick.BOF Then %>
<form name="form1" method="post" action="">
<table width="80%" border="1" cellspacing="0" cellpadding="0" align="center">
<tr>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff
No</font></b></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Name</font></b></div>
</td>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Team</b></font></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sick
From Date</font></b></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">No
of Sick Days</font></b></div>
</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsLTSick.EOF))
%>
<tr>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("STAFF_NO").Value)%></font></div>
</td>
<td nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("(P.FIRST_NAME||''||P.SURNAME)").Value)%></font></td>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("TEAM_REF").Value)%></font></div>
</td>
<td nowrap><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%= DoDateTime((rsLTSick.Fields.Item("FROM_DATE").Value), 1, 1033) %></font></td>
<% fromdate = (rsLTSick.Fields.Item("FROM_DATE").Value) %>
<% nowdate = Now %>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><% if DateDiff("d", fromdate, nowdate) >=28 then response.write ( Datediff ("d", fromdate, nowdate)) %></font></div>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsLTSick.MoveNext()
Wend
%>
</table>
</form>
<% End If ' end Not rsLTSick.EOF Or NOT rsLTSick.BOF %><% If rsLTSick.EOF And rsLTSick.BOF Then %>
<form name="form2" method="post" action="">
<b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">There are no
staff with open sick records.</font></b>
</form>
<% End If ' end rsLTSick.EOF And rsLTSick %>
<p> </p>
</body>
</html>
|
|
10-18-04, 20:41
|
|
Coffee Minion
|
|
Join Date: Nov 2003
Location: Sydney
Posts: 1,515
|
|
Note the added bold bits.... this should take care of things for you
Code:
<%@LANGUAGE="VBSCRIPT"%> <%
set rsLTSick = Server.CreateObject("ADODB.Recordset")
rsLTSick.ActiveConnection = "dsn=misp;uid=system;pwd=manager;"
rsLTSick.Source = "SELECT a.staff_no, a.from_date, (p.first_name || ' ' || p.surname), p.team_ref FROM absent a, pdb p WHERE a.to_date is null and a.staff_no = p.staff_no and p.leaving_date is null ORDER BY 2"
rsLTSick.CursorType = 0
rsLTSick.CursorLocation = 2
rsLTSick.LockType = 3
rsLTSick.Open
rsLTSick_numRows = 0
%><%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsLTSick_numRows = rsLTSick_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
' set the record count
rsLTSick_total = rsLTSick.RecordCount
' set the number of rows displayed on this page
If (rsLTSick_numRows < 0) Then
rsLTSick_numRows = rsLTSick_total
Elseif (rsLTSick_numRows = 0) Then
rsLTSick_numRows = 1
End If
' set the first and last displayed record
rsLTSick_first = 1
rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
' if we have the correct record count, check the other stats
If (rsLTSick_total <> -1) Then
If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
If (rsLTSick_numRows > rsLTSick_total) Then rsLTSick_numRows = rsLTSick_total
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them
If (rsLTSick_total = -1) Then
' count the total records by iterating through the recordset
rsLTSick_total=0
While (Not rsLTSick.EOF)
rsLTSick_total = rsLTSick_total + 1
rsLTSick.MoveNext
Wend
' reset the cursor to the beginning
If (rsLTSick.CursorType > 0) Then
rsLTSick.MoveFirst
Else
rsLTSick.Requery
End If
' set the number of rows displayed on this page
If (rsLTSick_numRows < 0 Or rsLTSick_numRows > rsLTSick_total) Then
rsLTSick_numRows = rsLTSick_total
End If
' set the first and last displayed record
rsLTSick_first = 1
rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
End If
%>
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
function DoDateTime(str, nNamedFormat, nLCID) dim strRet dim nOldLCID strRet = str If (nLCID > -1) Then
oldLCID = Session.LCID
End If
On Error Resume Next
If (nLCID > -1) Then Session.LCID = nLCID End If
If ((nLCID < 0) Or (Session.LCID = nLCID)) Then strRet = FormatDateTime(str, nNamedFormat) End If
If (nLCID > -1) Then
Session.LCID = oldLCID
End If
DoDateTime = strRet
End Function
</SCRIPT>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">There are <font color="#FF0033"><%=(rsLTSick_total)%></font>
staff with open sick records</font></b></p>
<p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff below
w1th no of sick days have exceeded 28 days sickness and are now Long Term Sick.</font></b>
</p>
<% If Not rsLTSick.EOF Or Not rsLTSick.BOF Then %>
<form name="form1" method="post" action="">
<table width="80%" border="1" cellspacing="0" cellpadding="0" align="center">
<tr>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff
No</font></b></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Name</font></b></div>
</td>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Team</b></font></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sick
From Date</font></b></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">No
of Sick Days</font></b></div>
</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsLTSick.EOF))
if DateDiff("d", fromdate, nowdate) >=28 then
%>
<tr>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("STAFF_NO").Value)%></font></div>
</td>
<td nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("(P.FIRST_NAME||''||P.SURNAME)").Value)%></font></td>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("TEAM_REF").Value)%></font></div>
</td>
<td nowrap><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%= DoDateTime((rsLTSick.Fields.Item("FROM_DATE").Value), 1, 1033) %></font></td>
<% fromdate = (rsLTSick.Fields.Item("FROM_DATE").Value) %>
<% nowdate = Now %>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><% if DateDiff("d", fromdate, nowdate) >=28 then response.write ( Datediff ("d", fromdate, nowdate)) %></font></div>
</tr>
<%
end if
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsLTSick.MoveNext()
Wend
%>
</table>
</form>
<% End If ' end Not rsLTSick.EOF Or NOT rsLTSick.BOF %><% If rsLTSick.EOF And rsLTSick.BOF Then %>
<form name="form2" method="post" action="">
<b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">There are no
staff with open sick records.</font></b>
</form>
<% End If ' end rsLTSick.EOF And rsLTSick %>
<p> </p>
</body>
</html>
|
|
10-18-04, 20:42
|
|
Coffee Minion
|
|
Join Date: Nov 2003
Location: Sydney
Posts: 1,515
|
|
you could also do this which I think will work and is a little better....
Code:
<%@LANGUAGE="VBSCRIPT"%> <%
set rsLTSick = Server.CreateObject("ADODB.Recordset")
rsLTSick.ActiveConnection = "dsn=misp;uid=system;pwd=manager;"
rsLTSick.Source = "SELECT a.staff_no, a.from_date, (p.first_name || ' ' || p.surname), p.team_ref FROM absent a, pdb p WHERE a.to_date is null and a.staff_no = p.staff_no and p.leaving_date is null ORDER BY 2"
rsLTSick.CursorType = 0
rsLTSick.CursorLocation = 2
rsLTSick.LockType = 3
rsLTSick.Open
rsLTSick_numRows = 0
%><%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsLTSick_numRows = rsLTSick_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
' set the record count
rsLTSick_total = rsLTSick.RecordCount
' set the number of rows displayed on this page
If (rsLTSick_numRows < 0) Then
rsLTSick_numRows = rsLTSick_total
Elseif (rsLTSick_numRows = 0) Then
rsLTSick_numRows = 1
End If
' set the first and last displayed record
rsLTSick_first = 1
rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
' if we have the correct record count, check the other stats
If (rsLTSick_total <> -1) Then
If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
If (rsLTSick_numRows > rsLTSick_total) Then rsLTSick_numRows = rsLTSick_total
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them
If (rsLTSick_total = -1) Then
' count the total records by iterating through the recordset
rsLTSick_total=0
While (Not rsLTSick.EOF)
rsLTSick_total = rsLTSick_total + 1
rsLTSick.MoveNext
Wend
' reset the cursor to the beginning
If (rsLTSick.CursorType > 0) Then
rsLTSick.MoveFirst
Else
rsLTSick.Requery
End If
' set the number of rows displayed on this page
If (rsLTSick_numRows < 0 Or rsLTSick_numRows > rsLTSick_total) Then
rsLTSick_numRows = rsLTSick_total
End If
' set the first and last displayed record
rsLTSick_first = 1
rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
End If
%>
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
function DoDateTime(str, nNamedFormat, nLCID)
dim strRet
dim nOldLCID
strRet = str
If (nLCID > -1) Then
oldLCID = Session.LCID
End If
On Error Resume Next
If (nLCID > -1) Then
Session.LCID = nLCID
End If
If ((nLCID < 0) Or (Session.LCID = nLCID)) Then
strRet = FormatDateTime(str, nNamedFormat)
End If
If (nLCID > -1) Then
Session.LCID = oldLCID
End If
DoDateTime = strRet
End Function
</SCRIPT>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">There are <font color="#FF0033"><%=(rsLTSick_total)%></font>
staff with open sick records</font></b></p>
<p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff below
w1th no of sick days have exceeded 28 days sickness and are now Long Term Sick.</font></b>
</p>
<% If Not rsLTSick.EOF Or Not rsLTSick.BOF Then %>
<form name="form1" method="post" action="">
<table width="80%" border="1" cellspacing="0" cellpadding="0" align="center">
<tr>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff
No</font></b></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Name</font></b></div>
</td>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Team</b></font></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sick
From Date</font></b></div>
</td>
<td nowrap>
<div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">No
of Sick Days</font></b></div>
</td>
</tr>
<%
Dim iNumSickDays
While ((Repeat1__numRows <> 0) AND (NOT rsLTSick.EOF))
iNumSickDays = DateDiff("d", fromdate, nowdate)
if iNumSickDays >=28 then
%>
<tr>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("STAFF_NO").Value)%></font></div>
</td>
<td nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("(P.FIRST_NAME||''||P.SURNAME)").Value)%></font></td>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("TEAM_REF").Value)%></font></div>
</td>
<td nowrap><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%= DoDateTime((rsLTSick.Fields.Item("FROM_DATE").Value), 1, 1033) %></font></td>
<% fromdate = (rsLTSick.Fields.Item("FROM_DATE").Value) %>
<% nowdate = Now %>
<td nowrap>
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"> <%=iNumSickDays%></font></div>
</tr>
<%
end if
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsLTSick.MoveNext()
Wend
%>
</table>
</form>
<% End If ' end Not rsLTSick.EOF Or NOT rsLTSick.BOF %><% If rsLTSick.EOF And rsLTSick.BOF Then %>
<form name="form2" method="post" action="">
<b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">There are no
staff with open sick records.</font></b>
</form>
<% End If ' end rsLTSick.EOF And rsLTSick %>
<p> </p>
</body>
</html>
|
|
10-19-04, 03:38
|
|
Useless...
|
|
Join Date: Jul 2003
Location: SoCal
Posts: 721
|
|
Oh my.. why don't you just put the DATEDIFF functions in the SQL?
SELECT DATEDIFF('d', fromdate, todate) AS SickDays, ... FROM ... WHERE DATEDIFF('d', fromdate, todate) >= 28 AND ...
Note, the ellipses (...) are representative of the rest of your query. fromdate and todate are place holders for the actuall columns which contain dates to be evaluated. If you are evaluation from a stored date and the current date, replace "todate" with "GETDATE()" and the server will call the GETDATE() function to insert the current date/time.
Now you only get the rows that are equal to or greater than 28 days, and it includes a column with that aggregate value. No ASP required! Much faster and efficient...
__________________
That which does not kill me postpones the inevitable.
|
|
10-19-04, 03:40
|
|
Useless...
|
|
Join Date: Jul 2003
Location: SoCal
Posts: 721
|
|
I should note that the SQL presented would work with MS SQL Server.. I can't guarantee it will work elsewhere... but there should be adequate alternatives for whatever database you're using... I think even MS Access would work with that query, but not positive... DATEDIFF in this context is not an ASP function, but instead, a SQL function, they just so happen to have the same function name...
__________________
That which does not kill me postpones the inevitable.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Datediff in ASP/Ultradev
