Jump to content
Sign in to follow this  
gritts

Problem with search of MSSQL database using datetime

Recommended Posts

gritts

I am attempting to create a script which will poll a SQL database that is part of our internal server monitoring application. The results would then be pushed to the local growl client on my desktop. I am successful at querying the SQL database using the SQL UDF and can send growl notifications fine as well...

What I run into is "Syntax error converting datetime from character string." when I try to query the database with the value from a previous query. (My appologies for making this sound confusing)

For example, I query the database and return the results into a 2D array. I can display the results fine with "_ArrayDisplay". I then take the results from the query and using the next to last row from the results, get the value from the datetime column.

$tmpDate = String($aData[$iRows-1][1])
MsgBox(0,"Date",$tmpDate)

It is when I take the value in $tmpDate and attempt to search with it that I get the syntax error above.

$iRval = _SQL_GetTable2D(-1, "select * from dbo.AllpagesToday WHERE PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL' AND Date > '" & $tmpDate & "' ORDER BY Date;", $aData, $iRows, $iColumns)

I have also attempted to convert the date format from: 20120319090303 to 2012-03-19 09:03:03.000 to no avail.

My conversion pieced together from other posts:

Func _convertToDateTime($dtString)
Local $newdt
$newdt = StringRegExpReplace($dtString, "A(d{4})(d{2})(d{2})(d{2})(d{2})(d{2})(?:.*)","$1-$2-$3 $4:$5:$6")
MsgBox(0,"Convert Results",$newdt)
Return($newdt)
EndFunc

When I search using the converted datetime, it is as if the query ignored the datetime value and returns all values of the 2D array.

$iRval = _SQL_GetTable2D(-1, "select * from dbo.AllpagesToday WHERE PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL' AND Date > '" & _convertToDateTime($tmpDate) & "' ORDER BY Date;", $aData, $iRows, $iColumns)

Any suggestions? If more information is needed, let me know.

Edited by gritts

Share this post


Link to post
Share on other sites
Juvigy

"select * from dbo.AllpagesToday WHERE PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL' AND Date > '"

This should be i think:

"select * from dbo.AllpagesToday WHERE (PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL') AND Date > '"

Test the SQL query somewhere outside outoit to see how/if it works.

Edited by Juvigy
  • Like 1

Share this post


Link to post
Share on other sites
gritts

Thank you Juvigy, I had thought about using parenthesis but did not try. Here is my resulting line in my script that works...

$iRval = _SQL_GetTable2D(-1, "select * from dbo.AllpagesToday WHERE (PageRecip = 'SERVER_OC1' OR PageRecip = 'SERVER_ONCALL') AND Date > '" & _convertToDateTime($tmpDate) & "' ORDER BY Date;", $aData, $iRows, $iColumns)
Edited by gritts

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Similar Content

    • nooneclose
      By nooneclose
      My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.
      This is what I have so far.
      Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill") _ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") _Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)  
      Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 
    • AndyS19
      By AndyS19
      I'm trying to implement a Ctl-F popup box that looks something like the one that Notepad uses, but I'm not havine much luck.  I intend to get it working, then beef up the popup's contents to add several checkboxes, buttons and radio boxes.
      What my example code does is to use InputBox(), but that's not what I want.
      Here is my test code:
      #include <Array.au3> #include <GUIConstantsEx.au3> #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 Opt("GUICloseOnESC", 1) Opt("GUIOnEventMode", 1) Opt('MustDeclareVars', 1) OnAutoItExitRegister("ExitStageLeft") Opt("WinTitleMatchMode", -2) Global $hGUI _Main() Func _Main() $hGUI = GUICreate("Test ^F", 300, 200) setupSpecialKeysHandlers() GUISetOnEvent($GUI_EVENT_CLOSE, "Event_GUIClose") GUISetState() While (1) Sleep(157) WEnd EndFunc ;==>_Main Func handle_CTRL_F_key() Local $str $str = InputBox("Search", "Enter the string to search for:") ConsoleWrite("+++: $str ==>" & $str & "<==" & @CRLF) EndFunc ;==>handle_CTRL_F_key Func ExitStageLeft() Exit (99) EndFunc ;==>ExitStageLeft Func Event_GUIClose() Exit (1) EndFunc ;==>Event_GUIClose Func setupSpecialKeysHandlers() Local $ar, $parts, $key, $handler, $id Local $aAccelKeys[1][2] ; Create a table of Special keys and their handlers $ar = StringSplit("", "") _ArrayAdd($ar, "^f - handle_CTRL_F_key ") ReDim $aAccelKeys[UBound($ar) - 1][2] ; Now, create $aAccelKeys array with the table data. ; For each entry, create a Dummy GUI and associate its ; ID with the special key. For $ndx = 1 To UBound($ar) - 1 $parts = StringSplit($ar[$ndx], "-", 2) $key = StringStripWS($parts[0], 8) $handler = StringStripWS($parts[1], 8) $id = GUICtrlCreateDummy() $aAccelKeys[$ndx - 1][0] = $key $aAccelKeys[$ndx - 1][1] = $id GUICtrlSetOnEvent($id, $handler) Next GUISetAccelerators($aAccelKeys) ; Setup the Special keys hooks EndFunc ;==>setupSpecialKeysHandlers  
    • Atoxis
      By Atoxis
      Howdy, I've gone through a lot of au3 forums, and I once had a working Imagesearch script that I got from here.  However, and i'm just totally not sure how but my imagesearch scripts aren't working anymore.
      I'm not new to au3 but i'm not the most experienced with it's syntax/commands.

      Anyways, I've looked over the big threads involving imagesearch.

      Does anyone have a working Imagesearch x64 for win10 that is currently working as of the date with the post.

      Dll's and what not is fine, just when I tell the script to run, I want to be able to find the image on the screen!
      Can't find a working copy so if anyone has one please send it my way lol.

      I've taken all the imagesearch downloads and what not and have played with them but I can't get any of them working on my end, despite others saying they're working.
      Thanks.
    • lenclstr746
      By lenclstr746
      HELLO GUYS
      I'm a work on a background see and click bot project 
      I can complete it if your help me
      (using imagesearch , gdi+ and  fastfind)
    • argumentum
      By argumentum
      I'm with MS SQL Express 2014.
      Went to the wiki - Databases_and_web_connections but I can't find a way =/
      the strings I've got are:
      ODBC;DSN=eFilmWorkstation;Trusted_Connection=Yes ODBC;DSN=eFilmWorkstation;UID=sa;PWD=eFilmWS30 "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';" A working example ? ( that would run if you had the db )
      Or, advise in a UDF to use and how to use it ?
      I don't care to write, just to read.
      Thanks
       
×