AutoIT and SQLEXPRESS

[JustSomeone]

Hello to everyone,

I am having some problems and/or lack of ideas while doing one project.

My question here is :

Is it possible to extract data from a database, in the DDMMYY HH:MM:SS format (read: date/month/year hours:minutes:secs). The data is stored in that way in the database, but i cannot find any way to import it in Autoit in that format.

I am using the SQL example located here>

and so far i can connect to the db and read the data, but nothing more.

This is my code >

#include <_sql.au3>
#include <array.au3>
Opt ("trayIconDebug",1)
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

If _sql_Connect(-1,"NKOLEV\SQLEXPRESS","QSS","","") = $SQL_ERROR then
Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
_SQL_Close()
Exit
EndIf
Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
$iRval = _SQL_GetTable2D(-1,"SELECT INDTime, OUTDTime, DurationInSec, BatchID FROM ParkSubscriptionIOEvents WHERE (BatchID=67);",$aData,$iRows,$iColumns)
If $iRval = $SQL_OK then _arrayDisplay($aData,"2D (" & $iRows & " Rows) (" & $iColumns & " Columns)" )
_SQL_Close()

I want somehow to calculate the DurationInSec values and convert it to hours (the data is stored in seconds). There is more things i will need to add in the future, but for now i want to make this work. Any help/ideas/suggestions are wellcome

[jchd]

I'm guessing that you mean that columns INDTime and OUTDTime are in the awkward DMYHMS format. Note that all dates in a DB should be in a computable format either ISO 8601 or integer or double, but certainly not in such a useless format. I'd use ISO 8601 in your case so that you can collate and compute using the _Date* functions.

You say you want to compute DurationInSec but aren't you getting it with your query since you say it's a duration in seconds?

[JustSomeone]

@jchd I get the time in this format : 20121023103447 and i need it to be 23.10.2012 10:34:47 or 2012.10.23 10:34:47. The other stuff i got how to make, but i am short on time right now, so i cannot make bigger posts with examples, maybe later today.

[jchd]

Ha, much clearer and simple. Try this:

Local $sDateIn = '20121023103447'
Local $sDateDispYMD = StringRegExpReplace($sDateIn, '(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)', '$3.$2.$1 $4:$5:$6')
ConsoleWrite($sDateDispYMD & @LF)
Local $sDateDispDMY = StringRegExpReplace($sDateIn, '(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)', '$1.$2.$3 $4:$5:$6')
ConsoleWrite($sDateDispDMY & @LF)

[JustSomeone]

Thanks alot, i will try that later. (had some other stuff on my head and i did not looked here for a while)