Firedac lost ftDate field type after using the SQLITE date() function

[Stéphane Wierzbicki 45]

Hello,

 

I have a problem that is driving me nuts. FireDac somehow "lost" the ftDate type after using the SQLite date function.

My SQLite table does have one field "DATE_OF_LOSS" defined as "DATE".

 

Field "DATE_OF_LOSS" will correctly be retrieved as a ftDate field type (for exemple 01/02/2018)

Select DATE_OF_LOSS from TABLE

This is no more the case when doing this. Field "DATE_OF_LOSS" will be retrieved as a ftString field type (for example 2018-02-01).

 select date(DATE_OF_LOSS,'start of month','+1 month','-1 day')   As DATE_OF_LOSS

Does anyone knows how to solve this ?

 

PS: using the latest Delphi RIO version, default SQLite connection parameters leaved to default. My OS Date time format is "dd/MM/yyyy"

Edited May 10, 2019 by Stéphane Wierzbicki
Added date output example

[Guest]

I do not use SQLLite. Checked the docs and perhaps date() actually returns a string, the return type is not specified (!).

I can see in the SQLLite docs that SQLLite also supports CAST.

If i do this in Firebird i get a DATE SQLType back:

 

"SELECT CAST(NULL as DATE) as COLUMNNAME from RDB$DATABASE".

Try explicit cast on the expression.

[Stéphane Wierzbicki 45]

Thank you Dany, forgot about CAST (and I heavily used it on mySQL many years ago...) 

That's strange. I'm getting year value "2018" instead of "01/02/2018" for example... 

select CAST(date(DATE_OF_LOSS,'start of month','+1 month','-1 day') as DATE)   As DATE_OF_LOSS

[Stéphane Wierzbicki 45]

Testing 

 

cast(date('now') as date),

 

will return 2019

[Stéphane Wierzbicki 45]

@Dmitry Arefiev do you have some advice on how to get this working? 

[Dmitry Arefiev 101]

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping

Read sentence "For an expression in a SELECT list ..."

[Stéphane Wierzbicki 45]

1 hour ago, Dmitry Arefiev said:

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping

Read sentence "For an expression in a SELECT list ..."

Well, thank you so much, I wasn't aware of this. This worked.