Using Date & Time in an SQL query

[Gary Williams]

Hi All,

I have written a vbs script that searches an access database and outputs the resulting recordset into an html table which I then email to myself so that each morning I have a list of enquirers sitting in my email in box, ready for processing.

The SQL query I use is this:

strSQL = "SELECT enquirynumber, email FROM enquiries WHERE ((enquiryentered>(#2005-06-09 18:00:00#)) AND (enquiryentered<(#2005-06-10 08:00:00#)));"

This works fine and returns a list of enquiriers who left details via my web form after 6pm on 9th June and before 8am on 10th June.

The problem is that the DateTime is hardcoded. If I only needed the dates, I could use the following to automatically increase the date each day:

(((enquiryentered>(Date()-1)) AND (enquiryentered<(Date()-0)))

but I need the time as well - 6pm yesterday and 8am today.

How do I get the Time into the sql query as well as the date?

Regards

Gary

[miranda]

put the values into variables then pass the variables in your SQL string

Dim date1 : date1 = (Date()-1) & " 18:00:00"
Dim date2 : date2 = Date() & " 08:00:00"
strSQL = "SELECT enquirynumber, email FROM enquiries WHERE ((enquiryentered>(#" & date1 & "#)) AND (enquiryentered<(#" & date2 & "#)));"

[Gary Williams]

Hi Miranda,

That is brilliantly simple! Trying it now.

Thanks

Gary

[Gary Williams]

Hi Miranda,

Can't get it working :(

The problem is the "<" in "AND (enquiryentered<(#" & date2 & "#)));"

The script seems to think the "<" is the start of a tag.

I've tried the "Between" statement but this doesn't work either

Regards

Gary

[Gary Williams]

Hi Miranda,

When I run the script, the cmd box opens, flashes an error message and immediately closes. How do you get the cmd box to freeze open so the message can be read?

Regards

Gary

[miranda]

If your first example works, so should this code. Have you tried doing a Response.Write(strSQL) to see what it is outputting?

[Gary Williams]

Hi Miranda,

Thanks for the tip. I found the error and here is the code that works.

------------------------------

strToday = FormatDateTime(Date(),1)
strYesterday = FormatDateTime(DateAdd("d", -1, Date()),1)
strYesterdayDayValue = (Day(Date)-1)
date1 = "#" & (Date()-1) & " 18:00:00#"
date2 = "#" & Date() & " 08:00:00#"

' Select the records to be written to the CSV file

strSQL = "SELECT * FROM proposals WHERE ( user = '1234' AND ((orderentered>(" & date1 & ")) AND (orderentered<(" & date2 & "))));"

----------------------------------

Thanks for the help.

Regards

Gary