Subtracting a set number of days from a Date/Timestamp

Posted on 2008-11-07
Last Modified: 2012-06-27
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

Question by:BenthamLtd
    LVL 7

    Expert Comment

    LVL 7

    Expert Comment

    DateAdd("d", -90, Date())
    LVL 6

    Expert Comment

    LVL 14

    Accepted Solution

    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


    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now