SQL Query in VBScript based on the date entered

Question

I have a VBscript that queries the database to pull data out that is based on the shift e.g. graveyard, day, and swing. I need to adjust the time by an hour less only on days that are past 1-6-2019.

The solution I have tried is expanding my if statement and adding an AND function but it won't work because the first if statement is still true.

dim intCoilCount, intTotalSeconds,intSeconds,strDate,strShift

'SQL="select timeStamp, coil_number, entry_gaptime,thickness,width_in,grade from TABLEEEE by timeStamp"
strShift=Request.Form("SHIFT")
strDate=Request.Form("StartDate")

'if date is greater than 2-22-2006 (switchover date) use SCALEFACTOR
'-----start-----------------
if datediff("d",strDate,cdate("2/22/2006")) <= 0 then
  SCALEFACTOR=30000.0 / 50.0
else
  SCALEFACTOR=1
end if

'-----end-----------------

'Fixed scale factor problem
'-----start-----------------
SCALEFACTOR=1
'-----end-----------------

SQL="select timeStamp, coil_number, entry_gaptime,thickness,width_in,grade from entryCoilData" 
if strShift="graveyard" then
    SQL = SQL & " where timestamp > '" & cdate(strDate)-1 & " " & "11:00PM" & "'" & _
                " and timestamp <= '" & strDate & " " & "7:00AM" & "'"

elseif strShift="graveyard" and strDate >= cdate(1-6-2019) then
    SQL = SQL & " where timestamp > '" & strDate & " " & "10:00AM" & "'" & _
                " and timestamp <= '" & strDate & " " & "2:00PM" & "'"

elseif strShift="day" then
    SQL = SQL & " where timestamp > '" & strDate & " " & "7:00AM" & "'" & _
                " and timestamp <= '" & strDate & " " & "3:00PM" & "'"

elseif strShift="day" and strDate >= cdate(1-6-2019) then
    SQL = SQL & " where timestamp > '" & strDate & " " & "7:00AM" & "'" & _
                " and timestamp <= '" & strDate & " " & "3:00PM" & "'"

else
    SQL = SQL & " where timestamp > '" & strDate & " " & "3:00PM" & "'" & _
                " and timestamp <= '" & strDate & " " & "11:00PM" & "'"
end if

Add the date condition to the first if statement also, `if strShift="graveyard" AND CDate(strDate) < CDate("1-6-2019") Then` (You need to cast the `strDate` value as it is a string. All `request.form` values are strings.). Also note that `1-6-2019` needs to be quoted. — Flakes, Jan 18 '19 at 08:31

score 1 · Accepted Answer · answered Jan 18 '19 at 15:57

I would keep that pesky logic out of the SQL string, and do that in vbscript. Something like this (untested):

dim givendate, startdatetime, enddatetime
givendate = cdate(strDate)
startdatetime = CDate(strDate & " " & "3:00PM")
enddatetime = CDate(strDate & " " & "11:00PM")

if strShift="graveyard" then
    if givendate >= cdate("1-6-2019") then
        startdatetime = CDate(strDate & " " & "10:00AM")
        enddatetime = CDate(strDate & " " & "02:00PM")
    else
        startdatetime = DateADD("d", -1, CDate(strDate & " " & "11:00PM"))
        enddatetime = CDate(strDate & " " & "07:00AM")
    end if
end if

if strShift="day" then
    startdatetime = CDate(strDate & " " & "07:00PM")
    enddatetime = CDate(strDate & " " & "03:00PM")
end if

SQL="SELECT timeStamp, coil_number, entry_gaptime,thickness,width_in,grade from entryCoilData" 
SQL = SQL & " WHERE timestamp > '" & startdatetime  & "'"
SQL = SQL & " AND timestamp <= '" & enddatetime  & "'"

response.write(SQL)

This way you just calculate the startdatetime and enddatetime parameters, and execute the same SQL for each case.

Please note that the way you write your SQL statements in ASP leaves you vulnerable to SQL injection attacks.

You might also want to consider writing date strings in ISO format (yyyy-mm-dd), that way the database will always understand the date. When you use cdate("1-6-2019"), this might be june first or january sixth, depending on how your database or OS is configured. When you use cdate("2019-6-1"), this is universally understood as june first.

I was definitely considering writing it on a stored procedure because it is a lot safer, but right now I just want to make it work. Thank you! — AlfredGG, Jan 21 '19 at 15:35
@AlfredGG cool; if my answer works for you, please accept is as a solution. — Erik Oosterwaal, Jan 22 '19 at 11:09
So many misconceptions in this answer. You're right that the regional settings of the server can effect how the how `CDate()` outputs for example `01/04/2019` versus `04/01/2019` but once it gives a result there is no guarantee it will be interpreted the same way by the database server. What I mean is if you pass an ISO style date into `CDate()` it will convert it to a VBScript `Date` object which then needs to be transformed again into an ISO date string before it is passed into the database query. — user692942, Jan 26 '19 at 09:24
The best way to handle this is pass the VBScript dates into the query as date parameters of a `ADODB.Command` object that way there is no ambiguity as to what dates are passed to the query and no need to convert them once more to ISO date strings. — user692942, Jan 26 '19 at 09:27
I was referring to passing strings as dates to the database, because dates are often strings originating in a web-form. but admitted, I can only verify this for SQL server. When passing an ISO formatted datestring in a Query, SQL server will always interpret it correctly. But you are very right that a cdate and an ADODB command is the best way to go. — Erik Oosterwaal, Jan 26 '19 at 19:16

SQL Query in VBScript based on the date entered

1 Answers1