• Welcome, Guest. Please login.
 
July 08, 2020, 02:56:31 am

News:

Welcome to the SQLitening support forums!


SQLite Date Functions

Started by Fredrick Ughimi, January 31, 2011, 05:18:43 am

Previous topic - Next topic

Fredrick Ughimi

Hello, how does one use the JULIANDAY or STRFTIME functions in SQLitening? I get missing declaration error. Best regards
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

I'm having problems using JULIANDAY to query records BETWEEN two Dates using string variables to store dates. 
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

The code:  slSEL "SELECT * FROM tblDiagnosis WHERE Date BETWEEN JULIANDAY(sFrom) AND JULIANDAY(sTo)"
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

I get error - no such column: sTo
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

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:
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 O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Fred, in the solution above sFrom & sTo are not being converted to JULIANDAY. Tested both variables.   
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Bern, you could be right. But I need to do a date range.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

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:


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

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

   
   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)