AutoIT SQLIite3 Order by Date

[Eminence]

Hello,

I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date.

This is my current code:

Local $iCurrentDate = _Now()

Local $sqlHandle = _dbOpen($sDbPath)
Local $aResult, $iRows, $iCols

_SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols)
_ArrayDisplay($aResult)
_dbClose($sDbpath)

What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance.

 

*EDIT

The date format in the database is by MM/DD/YYYY HH:MM:SS.

Edited May 9, 2018 by Eminence

[TheXman]

I think you answered your question with the additional information that you provided.

You need to spend some time understanding the differences between SQLite and other relational databases like MySQL, MS SQL, Oracle, etc.  First of all, SQLite does not have a datatype or storage type of DATE.  Everything in a SQLite database is stored as either null, text, blob (binary), or a number (real or integer).  Second, if you read the sqlite documentation on the datetime function, you will see that "mm/dd/yyyy hh:mm:ss" is not an acceptable time string.

If you do not want to have to go thru all sort of conversion gymnastics, it would be much easier to store your dates in a commonly accepted SQLDATE format (YYYY-MM-DD HH:MM:SS) or numeric format like Julian or Epoch.  As far as SQLite is concerned, your dates are strings and are being sorted as such.  Obviously it cannot sort your dates correctly if the are stored in a MM/DD/YYYY format.

You can start learning more about SQLite datatype and date/time functions by going to the following links:

https://www.sqlite.org/datatype3.html
https://www.sqlite.org/lang_datefunc.html

[Eminence]

Oh my bad. Yep, understood now. Thanks @TheXman