Format Date for SQL query 3

Shelby500 · Oct 25, 2013

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?

Code:

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 · Oct 25, 2013

Where the stpTime and strTime are in the SQL query is where the date/time will be insered. The formate of the date/time needs to look like this.

Code:

2013-10-23 10:44

Geates · Oct 28, 2013

Does the script run every 15 minutes right now? If not, I have two suggestions.

1. Create a scheduled task set to run your script every 15 minutes. Correctly format your timestamp based on date at runtime.
2. Wrap the portion of code in a loop that executes every 15 minutes.

To begin, I would use strongm vbsFormat ([link thread329-1570966]vbsFormat[/url]) function to correctly format your timestamp.

Code:

Public Function vbsFormat(Expression, Format)
    vbsFormat = CoreFormat("{0:" & Format & "}", Expression)
End Function

' Allows more of the .NET formatting functionality to be used directly if required
Public Function CoreFormat(Format, Expression)
    CoreFormat = Expression
    On Error Resume Next
    With CreateObject("System.Text.StringBuilder")
        .AppendFormat Format, Expression
        If Err=0 Then CoreFormat = .toString
    End With
End Function

[b][COLOR=#CC0000]msgbox vbsFormat(now, "yyyy-MM-dd hh:mm")[/color][/b]

For a loop that runs every 15 minutes on the dot, observe that 15 minutes is 900 secs. timer() tells you how many seconds have passed since the beginning of the day. Therefore, to get number of seconds since the next 15 minute interval, we modulate timer() by 900. Then subtract that value from 900 to get seconds until the next interval. Wait until the next interval before looping (wscript.sleep uses millisecconds so we'll want to multiply by 1000).

Code:

do[s][/s]
   <your code>
   [COLOR=#CC0000]sec_since = (timer mod 900)[/color]
   [COLOR=#00CC00]sec_until = 900 - since[/color]
   [COLOR=#0000CC]wscript.sleep (sec_until * 1000)[/color]
   msgbox vbsFormat(now, "yyyy-MM-dd hh:mm")
loop

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Geates · Oct 28, 2013

typo...

sec_until = 900 - sec_since

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Shelby500 · Oct 28, 2013

I wanted to give a little more detail. The script is setup to run every 15 minutes via Windows task Scheduler. I need the script to run every 15 minutes but I need the SQL to change it's times in 15 minute intervals. So for example.

SQL:

Select * from table where start_time between 10:15am and 10:30am

. Then the next time it runs it would be

SQL:

select * from table where start_time between 10:30am and 10:45am

. So on and so forth. The examples are very simple but I wanted to keep any confusion down.

Geates · Oct 29, 2013

you can still use what I've already suggested with dateAdd() to accomplish you needs

Code:

sec_since = timer mod 900
sec_until = 900 - sec_since
fromDate = dateAdd("s", (timer - sec_since), date())
toDate = dateAdd("S", (timer + sec_until), date())

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

strQuery = "select * from table where start_time between " & strFromDate & " and " & strToDate

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Shelby500 · Oct 29, 2013

I'll post my final script as soon as I get it all put together and tested.

Shelby500 · Oct 29, 2013

Works perfectly. One more question. I need to get the sum total for each SQLArray(2,i). Is there a vbscript function that will do this? See below for the code I have so far for this piece of it. Is there something like this

Code:

If sum(SQLArray(2,i)) = "0" then

??

Code:

for i = 0 to ubound(SQLArray,2)
	If SQLArray(2,i) = "0" 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 & "<H2><b>Submit Numbers - Zero in last 15 minutes</H2>"
		sendemail "blah@blah.com","blah@blah.com", "blah@blah.com", "TESTING Submit Numbers - Quiet last 15 minutes - " & date, strResults
		Wscript.quit
	else
		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>"
	End If
next
strResults = strBegin & strHead & strRows & "</TABLE>"

Shelby500 · Oct 29, 2013

Do you think this function would work?

Code:

Function ArraySum(arr)
    Dim index
    
    First = LBound(arr)
    Last = UBound(arr)

    For index = First To Last
        ArraySum = ArraySum + arr(index)
    Next
End Function

Geates · Oct 29, 2013

yeah, it's call "addition"

you can make a sum() function that loops through the array and adds the value of each index.

Code:

function sum(arr, index)
   sum = 0
   for i = 0 to ubound(arr, (index + 1)) 'ubound() is ordinal (starts with 1) whereas the array index we pass in is cardinal (starts with 0)
      sum = sum + arr(index, i)
   next
end function

'EXAMPLE
dim var(1, 1)
var(0, 0) = 1
var(0, 1) = 2
var(1, 0) = 3
var(1, 1) = 4
msgbox sum(var, 1) 'output is 7

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Shelby500 · Oct 29, 2013

I'm not sure what I'm doing wrong but I keep getting type mismatch errors for ubound. I figure it is something simple but can't seem to find it.

Code:

for i = 0 to ubound(SQLArray,2)
	If Sum(SQLArray(2,i),0) = "0" 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 & "<H2><b>Submit Numbers - Zero in last 15 minutes</H2>"
		sendemail "blah@blah.com","blah@blah.com", "blah@blah.com", "TESTING Submit Numbers - Quiet last 15 minutes - " & date, strResults
		Wscript.quit
	else
		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>"
	End If
next
strResults = strBegin & strHead & strRows & "</TABLE>"

Shelby500 · Oct 29, 2013

I changed my code to look like this below but now it just stays in an endless loop. Am I doing something incorrectly with the index?

Code:

for i = 0 to ubound(SQLArray,2)
	If Sum(SQLArray,0) = "0" 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 & "<H2><b>Submit Numbers - Zero in last 15 minutes</H2>"
		sendemail "blah@blah.com","blah@blah.com", "blah@blah.com", "TESTING Submit Numbers - Quiet last 15 minutes - " & date, strResults
		Wscript.quit
	else
		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>"
	End If
next
strResults = strBegin & strHead & strRows & "</TABLE>"

Shelby500 · Oct 29, 2013

I've got it working but there is another problem. The server that I'm running the script on is in the Pacific time zone and my database where I'm pulling the data is in the Central time zone. How can I update the code to use Central time? Is it even possible?

Shelby500 · Oct 29, 2013

Got it. Here is the change.

Code:

fromDate = dateAdd("h",2,dateAdd("s", (timer - sec_since), date()))
toDate = dateAdd("h",2,dateAdd("S", (timer + sec_until), date()))

Shelby500 · Oct 30, 2013

Geates, what would cause the script to send multiple emails if it has no records or a "0" sum? Doing some testing but haven't found it yet.

Geates · Oct 30, 2013

Quite elegently, because 0 = "0"

-Geates

PS. The way I wrote the function, sum() returns 0 by default. Because 0 = "0" (your should compare an integer to an integer, not a string), the block of code that sends email is executed [tt]i[/tt] amount of times (for i = 0 to ubound(SQLArray,2) ). Find out what is causing the sum() function to return 0. Is it the function itself (i.e. the array being passed in is bad [ubound(arr, (index + 1)) equates to nothing or 0 so there is no [tt]for loop[/tt]. Therefore, a 0 is returned]). Or is it that the sum of all of the array value in the specified index is actually 0.

Make these modifications first:

Code:

function sum(arr, index)
   [COLOR=#FF0000]sum = -1[/color]
   for i = 0 to ubound(arr, (index + 1)) 'ubound() is ordinal (starts with 1) whereas the array index we pass in is cardinal (starts with 0)
      sum = sum + arr(index, i)
   next
end function

*this way the sum() function returns a -1 if the array cannot be correctly parsed

and

Code:

for i = 0 to ubound(SQLArray,2)
	[COLOR=#FF0000]msgbox sum(SQLArray, 0)[/color]	
	[COLOR=#FF0000]If Sum(SQLArray,0) = 0 then[/color]
		...
	End If
next

*see what the function returns. 0 (the actual sum) or 1 (a bad array)

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Geates · Oct 30, 2013

also look at that fact that [tt]sendemail "blah@blah.com","blah@blah.com", "blah@blah.com"[/tt] is sending 3 emails [tt]ubound(SQLArray,2) times[/tt]

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Shelby500 · Oct 30, 2013

I figured out the sendmail. The first one is the TO field the second is the CC field and the third one is the FROM field. I removed the CC email and it is only sending one now. However, last night it did send four emails a few different times.

Shelby500 · Oct 30, 2013

It returns the total number for every record in the data. In my example case it was 6 times.

Shelby500 · Oct 30, 2013

Just an FYI. The total of the count should actually be 10 rather than 29.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Format Date for SQL query 3

IS-IT--Management

IS-IT--Management

Programmer

Programmer

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

IS-IT--Management

IS-IT--Management

IS-IT--Management

Programmer

Programmer

IS-IT--Management

IS-IT--Management

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor