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

    • 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
       
    • dadalt95
      By dadalt95
      Perform a simple google search!
      The script below works fine until fill the google form!
      What I can't find is how to submit the form, tried a couple of ways and none of them worked.

       
      #include <IE.au3> $oIE = _IECreate ("www.google.com") $o_form = _IEFormGetObjByName ($oIE, "f") $o_login = _IEFormElementGetObjByName ($o_form, "q") $username = "80251369" _IEFormElementSetValue ($o_login, $username) $o_numer = _IEGetObjByName($o_form, "btnK") _IEAction ($o_numer, "click")  
      The code runs without any problem.
      I don't know how to proceed!
      Thanks in advance!
    • nacerbaaziz
      By nacerbaaziz
      Hi dears
      how are you? I hope You fine
      I have a question please
      I've created a listView
      It has several columns
      Is there any way  to search for text in an element of this list with text in all columns
      for example
      list view with 2 column
      the first is the file name and the second is the file path
      and i want to search for the item witch Containt the name and the path toGether
      I searched a lot but could not find what I was looking for
      If you do not understand the idea that I'm looking for, I can put an example
      Thanks in advance
    • Gowrisankar
      By Gowrisankar
      Hello everyone,
      I'm working on a task where, a PDF file is opened (in IE browser) when I click a link in a website.
      I have to read the first page of the PDF to find particular strings. Can you please share some ideas?
×