• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Date Formats and Queries

Started by Fredrick Ughimi, July 25, 2010, 08:05:28 AM

Previous topic - Next topic

Fredrick Ughimi

Hello,

I have a weird situation here with different Date Formats/Queries. When I use this query:


slSEL "SELECT * FROM tblCreditSales WHERE InvoiceNo = '" + sInvoiceNo + "' AND Date = '" + sDate + "'"

and my date format is "yyyyMMdd"

My query returns nothing. But when my date format is "yyyy-MM-dd", my query returns ok.

Then again, when my query is:


slSEL "SELECT * FROM tblCashSales WHERE Date BETWEEN " & sFrom & " AND " & sTo & ""

date format is "yyyyMMdd"
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet


Fredrick Ughimi

I have seen that before. Wasn't much help.

Thank you.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

SQLite has no "Date" data type.  You can store dates anyway you like.  The
way your Selects are working implies your dates are stored as 'YYYY-MM-DD'. 
This is the preferred way to store dates if you plan on using any of the
SQLite DateAndTimeFunctions. 

Fredrick Ughimi

Hello,

Solved the problem.

Discovery:


slSEL "SELECT FROM tblName WHERE Date BETWEEN " & sFrom & " AND " & sTo & " "


Works with "yyyyMMdd". While this query:


slSel "SELECT * FROM tblName+ WHERE Date BETWEEN '" + sFrom + "' AND '" + sTo + "'


Works with "yyyy-MM-dd".

Strange, I would say. Someone could try them to confirm.

Kind regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

Could you post a small database and a compilable program of this situation? 

Fredrick Ughimi

#6
This cuts across all my tables in all my applications.

Would post one soon.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Bern Ertl

Looks like you need to be more careful with whether you are storing information as a number or a string.

Personally, I prefer to store dates as QUAD numbers (using the Win API's FILETIME format).  It's easy to do rudimentary math on the dates in SQL statements with this format too.

Fredrick Ughimi

Hi Bern,

Thanks for the advice. Would take a look at that and see how it goes.

I have been storing Dates as null terminated string. Then  I initialise it this way:

Let  szDate = "yyyy-MM-dd"

Kind regards,


Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Bern,

>>Personally, I prefer to store dates as QUAD numbers (using the Win API's FILETIME format).  ...

I would appreciate it if you could post sample codes on how u do this.

Kind regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

David L Morris

Fredrick, you might look again at the date and time functions available in SQLite. Quote - you may choose to represent your datetimes in the database as Julian dates or as datetime formatted strings.  Julian dates are doubles but they are converted to and from string when written to and read from the database.

Representating your datetimes as Julian dates has the advantage that
1. you get the most time resolution for the fewest bytes
2. you can compute datetime differences with simple arithmetic
3. it is the native format inside the SQLite date and time functions
4. it is the format returned from the internal now() function

Unquote


Bern Ertl

Not a complete example, but should get you going...

'Important equates
%OneDay = 864000000000&&
%OneHour = 36000000000&&

UNION Quad_FileTime
   Q AS QUAD
   F AS FILETIME  'defined in Win32API.INC
END UNION

FUNCTION qFormat( BYVAL q AS QUAD) AS STRING

   IF q > 100000&& THEN
      FUNCTION = FORMAT$( q \ 100000&&) + FORMAT$( q MOD 100000&&)
   ELSE
      FUNCTION = FORMAT$( q)
   END IF

END FUNCTION
...

LOCAL ST AS SYSTEMTIME   'defined in Win32API.INC
LOCAL QF AS Quad_FileTime

GetLocalTime  ST    'Windows API function
SystemTimeToFileTime  ST, QF.F   'Windows API function

slExe "UPDATE table SET DateField = " + qFormat( QF.Q) + " WHERE RowID = 1"