• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 837
  • Last Modified:

Subtracting a set number of days from a Date/Timestamp

Dear EE community,

Just for my peace of mind, would you please check that I'm doing this properly. Basically I'm trying to subtract a set number of days from a DateTime or Timestamp. This value then gets used in the WHERE on the SQL query.

In this example, excluding all customers that have been contacted within the last 90 days: now() - 90?

Please see below for what I am currently using in terms of code.

Thank you.
<!-- snippet of code -->
 
<%
Dim rsQuery
Dim rsQuery_numRows
 
SQLQuery = "select QueryID, QueryName, ExcludeStartDate, ExcludeEndDate, IncludeStartDate, IncludeEndDate, ExcludeContactedDays, PartCodes from storedqueries where QueryID = '"&Session.Contents("scQID")&"'"
 
Set rsQuery = OBJdbConn2.Execute(SQLQuery)
 
rsQuery_numRows = 0
%>
<%
Dim mydate
Dim myday, myhour
Dim myminute, mysecond, myyear, mymonth
mydate = Now() '-- returns the current date and time
 
myday = Day(mydate) '-- returns the day (int; 1-31)
myhour = Hour(mydate) '-- returns the hour (int; 0-23)
myminute = Minute(mydate) '-- returns the minute(int; 0-59)
mysecond = Second(mydate) '-- returns the second (int; 0-59)
myyear = Year(mydate)'-- returns the year (int)
mymonth = Month(mydate)'-- returns the month (int;1-12)
 
Dim ExcStartDate
Dim ExcEndDate
Dim IncStartDate
Dim IncEndDate
Dim ExcContactedDays
Dim SPartCodes
 
 
QueryName = rsQuery("QueryName")
ExcStartDate = rsQuery("ExcludeStartDate")
ExcEndDate = rsQuery("ExcludeEndDate")
IncStartDate = rsQuery("IncludeStartDate")
IncEndDate = rsQuery("IncludeEndDate")
ExcContactedDays = myday-rsQuery("ExcludeContactedDays") '-- THIS IS THE ONE HERE HERE HERE!
SPartCodes = rsQuery("PartCodes")
 
%>
 
<!-- more codey stuff and then the SQL bit -->
<!-- snippet of code again -->
 
<% 
If ExcContactedDays <> "" Then
pm.SetSource = pm.SetSource & "  `callnotes`.`CallBackStatus` NOT IN ('2', '3', '4', '5', '6', '7', '9', '10') and "
pm.SetSource = pm.SetSource & "  `callnotes`.`CreatedDate` >= '"&ExcContactedDays&"' and "
End If
%>

Open in new window

0
BenthamLtd
Asked:
BenthamLtd
1 Solution
 
ASPSQLServerCOMCommented:
0
 
ASPSQLServerCOMCommented:
DateAdd("d", -90, Date())
0
 
psadacCommented:
just drop most of your VB code and use pure SQL :
pm.SetSource = pm.SetSource & "  `callnotes`.`CreatedDate` >= NOW() - INTERVAL 90 DAY and "

Open in new window

0
 
BenthamLtdAuthor Commented:
Well I'll be damned. I've been doing SQL for a while now and didn't even know that command existed. Boy I feel stupid. Thank you "psadac" and thank you everyone for all the ideas. Especially the VBDate manipulation. That is also worth it's weight in gold.

Kindest regards.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now