Format Date for SQL query 3

[Shelby500]

Below is my code. I need to create some variables that I can put in my SQL query. They need to increment by 15 minutes each time the script runs....therefore pulling data every 15 minutes. Below is what I have so far. I'm just not sure how to do the incrementing each time the script runs?

Dim strCon
Dim SQLArray
Dim strTime
Dim stpTime

theDate = date()
newDate = DateAdd("D", -1, theDate)
strYear = datepart("yyyy", Now())
strMonth = datepart("m", Now())
strDay = datepart("d", Now())
strHour = datepart("h", Now())
strMin = "00"
strMin2 = "15"
strMin3 = "30"
strMin4 = "45"

strTime = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin
strTime2 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin2
strTime3 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin3
strTime4 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin4

strCon = "Data Source=<servername>:3203/SE001P;password=passwd;User ID=userid;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;"

Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon
'Has data
Set oRs = oCon.Execute("SELECT TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI'), count(*) FROM t_messaging_log lg WHERE lg.logtime > TO_DATE('"&strTime&"', 'YYYY-MM-DD HH24:MI') AND lg.logtime < TO_DATE('"&stpTime&"', 'YYYY-MM-DD HH24:MI') AND lg.FUNCTIONCATEGORY = 'AgentApp-heAppSubmissionDelegate'   GROUP BY TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI') ORDER BY TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI')")

If oRs.BOF or oRs.EOF = "True" then
	strEmpty = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;><TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT(*)",50) & "</center></TD></TR></TABLE>"
	strResults = strEmpty & vbCr & "<H1><b>Sales Express ACAPS Submit Numbers - Zero in last 15 minutes/H1>"
	sendemail "blah@blah.com","blah@blah.com", "blah@blah.com", "TESTING Submit Numbers - Quiet last 15 minutes - " & date, strResults
	Wscript.quit
Else
SQLArray = oRs.GetRows(,,Array(CStr("TO_CHAR(LG.LOGTIME,'HH24')"),CStr("TO_CHAR(LG.LOGTIME,'MI')"),CStr("COUNT(*)")))
End If

'Begin writing array to variable

strBegin = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;>"
strHead = "<TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT(*)",50) & "</center></TD></TR>"
strRows = ""

for i = 0 to ubound(SQLArray,2)
   strRows = strRows & "<TR style=WORD-BREAK:BREAK-ALL;><TD NOWRAP><center>" & SQLArray(0, i) & "</center></TD><TD NOWRAP><center>" & SQLArray(1, i) & "</center></TD><TD style=word-wrap: break-word;><center>" & SQLArray(2, i) & "</center></TD></TR>"
next
strResults = strBegin & strHead & strRows & "</TABLE>" 

oCon.Close
Set oRs = Nothing
Set oCon = Nothing

'Send email notification function
function sendEmail (strTo, strCC, strFrom, strSubject, strBody)
   set objEmail = CreateObject("CDO.Message")
   objEmail.From = strFrom
   objEmail.To = strTo
   objEmail.CC = strCC
   objEmail.Subject = strSubject
   objEmail.HTMLBody = strBody
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "smtpservername"
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
   objEmail.Configuration.Fields.Update
   objEmail.Send
end function

 

[Shelby500]

I hate to keep this thread going but I seem to have an issue with this part of code. The script seemed to be working fine but now it is sending the email saying that the data is empty. I checked it and it is hitting the code below. If I try to remove the code it gives an error saying that BOF or EOF is true. The code seems like it would always be true.

If oRs.BOF or oRs.EOF = "True" then
	strEmpty = ""
	strResults = strEmpty & vbCr & "<H2><b>Submit Numbers - Zero in last 15 minutes</H2>"
	sendemail "blah@blah.com","", "blah@blah.com", "TESTING Submit Numbers - Quiet last 15 minutes - " & date, strResults
	Wscript.quit
Else
SQLArray = oRs.GetRows(,,Array(CStr("TO_CHAR(LG.LOGTIME,'HH24')"),CStr("TO_CHAR(LG.LOGTIME,'MI')"),CStr("COUNT(*)")))
End If

 

[Shelby500]

@strongm - How do you get your funcion to use 24 hour format rather than 12 hour?

 

[strongm]

http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

But n summary: "HH:mm:ss" rather than "hh:mm:ss

 

[Shelby500]

Got it.....uppercase HH. Makes sense.

 

[Shelby500]

Geates, I've had a hard time adjusting the timer part of the code to just do the last 30 minutes of data. The time change really messed it up since it is Pacific time zone and the database it pulls from is central time. I will assume there really isn't a way to automatically adjust for the time change in a situation like this?

 

[Shelby500]

I figured it out. I was running the script every 15 minutes and the timer was set to every 30 minutes (1800). Once I changed the interval the script runs it sorted itself out. I also had to change the "n" of the datepart to be "-15" to ensure it was not checking for a date that didn't exist yet. The last part I'm not sure why I had to do that but it worked so I'll leave it at that.

 

[Shelby500]

Just a note for anyone that uses this thread in the future. The timer function works great but if you are going to use another app to schedule the script then it is unesscessary. I used the code below to just subtract 30 minutes from the date/time in order to get my variables for my SQL query. I did contine using the vbsformat function as you can see below. This may not be the ideal way but it works great. I used "-31" and "-1" as my preference. You can use "-30" and no dateAdd for the toDate if you like. Thanks to Geates and PHV for the assistance.

thenow = now()

fromDate = dateAdd("h",3,dateAdd("n",-31,thenow))
toDate = dateAdd("h",3,dateAdd("n",-1,thenow))

strFromDate = vbsFormat(fromDate, "yyyy-MM-dd HH:mm")
strToDate = vbsFormat(toDate, "yyyy-MM-dd HH:mm")