SQLite Date Functions

[Fredrick Ughimi]

Hello, how does one use the JULIANDAY or STRFTIME functions in SQLitening? I get missing declaration error. Best regards

[Fredrick Ughimi]

I'm having problems using JULIANDAY to query records BETWEEN two Dates using string variables to store dates. 

[Fredrick Ughimi]

The code: 

Code Select Expand

slSEL "SELECT * FROM tblDiagnosis WHERE Date BETWEEN JULIANDAY(sFrom) AND JULIANDAY(sTo)"

[Fredrick Ughimi]

I get error - no such column: sTo

[Bern Ertl]

Is the Date field stored as a Julian number?  If not, you probably need to use JULIANDAY( Date).

[Fred Meier]

If sFrom and sTo are string variables then the syntax would be:

Code Select Expand

slSEL "SELECT * FROM tblDiagnosis WHERE Date BETWEEN JULIANDAY(" & sFrom & ") AND JULIANDAY(" & sTo & ")"

[Fredrick Ughimi]

Hi Fred, thank you.     Hi Bern, the date is stored in YYYY-MM-DD format. Still not getting the correct result. 

[Fredrick Ughimi]

Fred, in the solution above sFrom & sTo are not being converted to JULIANDAY. Tested both variables.   

[Fredrick Ughimi]

Bern, you could be right. But I need to do a date range.

[Fred Meier]

Date must be stored as JulianDay for your select to work.  JulianDay is
the number of days since noon in Greenwich on November 24, 4714 B.C.  So
today 2011-02-01 will return 2455593.5. 

[Fredrick Ughimi]

Fred,

Oh ok. That means one would have to write a Function/Method to do that since JULIANDAY won't work like this:

Code Select Expand


          slExe "Begin Immediate"
                 slExe slBuildInsertOrUpdate("tblDiagnosis", sHospitalNo & $Nul & JULIANDAY(sDate) & $Nul & sDiagnosis & $Nul & sCodeNumber)
            slExe "End"
           


Or is there a special way to do this with JULIANDAY.

Best regards,

[Fred Meier]

Here is some code that will insert a SQLite julian day into two records. 
Note the required $VT concatenated at end.  This will tell
slBuildInsertOrUpdate that its an expression rather that a value.  See the
slBuildInsertOrUpdate statement doc in SQLitening help file.  Also see
http://www.sqlite.org/lang_datefunc.html

Code Select Expand

   
   Local sDate as String
   slOpen ""
   slExe "Create table t1(f1)"
   sDate = "'2011-02-03'"
   slExe slBuildInsertOrUpdate("t1", "JULIANDAY(" & sDate &")" & $VT)
   sDate = "'2011-02-04 10:10'"
   slExe slBuildInsertOrUpdate("t1", "JULIANDAY(" & sDate &")" & $VT)