SQLite Functions

[GreenMeanie]

Hi all,
I have leaped into UniDAC and have achieved basic reading and writing from a SQLite db.
I now want to do some date calculations.
In the help file I see there is a group of Date functions, i.e. DATEADD and DATEDIFF.

I am not able to work out how to use them.
This is my SQL in a TUniQuery.

Code: Select all

  WHEN tblRecords.StartTime >= :FirstDT THEN tblRecords.StartTime
  END AS ST,
  
  CASE
    WHEN tblEndTimes.EndTime > :LastDT THEN :LastDT
    WHEN tblEndTimes.EndTime <= :LastDT THEN tblEndTimes.EndTime
  END AS ET,

  tblRecords.ActId,

  {fn DATEDIFF (hour, ST, ET)} AS Hrs   
  
FROM tblRecords, tblEndTimes
WHERE (tblRecords.RId = tblEndTimes.RId) AND
    ((tblRecords.StartTime >= :MinDT) AND (tblEndTimes.EndTime <= :MaxDT))
ORDER BY tblRecords.StartTime;

On clicking the OK button after entering the SQL I receive this error:

Unknown function: DATEDIFF.

Do I have to load this function some how? Or is this not available with the SQLite side of UniDAC.

If I have this all wrong, may I have some sample code?

Thanks in advance.

[ZEuS]

Hello.

UniDAC provides most common interface for working with different databases. The list of functions described in the help is a general list of functions provided by different databases, and not all of them are available in SQLite.
But UniDAC provides access to the mechanism of user-defined functions of SQLite, so you can define functions like DATEDIFF by yourself and use them in your queries.
Here is an example that demonstrates the implementation of the DATEDIFF function.
First you should create the implementation of the function somewhere in your code:

Code: Select all

function DateDiff(InValues: array of Variant): Variant;
var
 lDate1Str, lDate2Str: string;
 lDate1, lDate2: TDateTime;
 lDateSettings: TFormatSettings;
 lDatePart: string;
begin
 lDate1Str := VarToStr(InValues[1]);
 lDate2Str := VarToStr(InValues[2]);
 if (lDate1Str = '') or (lDate2Str = '') then
   Exit;
 GetLocaleFormatSettings(GetUserDefaultLCID, lDateSettings);
 lDateSettings.DateSeparator := '-';
 lDateSettings.TimeSeparator := ':';
 lDateSettings.ShortDateFormat := 'yyyy-mm-dd';
 lDateSettings.ShortTimeFormat := 'hh:nn:ss';
 try
   lDate1 := StrToDateTime(lDate1Str, lDateSettings);
   lDate2 := StrToDateTime(lDate2Str, lDateSettings);
 except
   Result := 'DATEDIFF: parameter(s) error';
   Exit;
 end;
 lDatePart := LowerCase(VarToStr(InValues[0]));
 if lDatePart = 'hour' then
   Result := HoursBetween(lDate1, lDate2)
 // else if lDatePart = 'year' then
 // ...
 ;
end;

Then you should register the function after opening the database connection:

Code: Select all

UniConnection1.Connected := True;
TLiteUtils.RegisterFunction(UniConnection1, 'DATEDIFF', 3, DateDiff);

Now you can use the DATEDIFF function in your queries like this:

Code: Select all

select DATEDIFF('hour', ORDER_DATE, SHIP_DATE) from SALES 

[GreenMeanie]

Finally found sometime to pick up where I left off.
Thanks very much Zeus.
This is an excellent example and was very easy for me to follow.

A wonderful product.

Thanks again.

[ZEuS]

Thank you for your interest in our products.
Feel free to contact us if you have any further questions about UniDAC.