Jump to content
Sign in to follow this  
Stéphane Wierzbicki

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

Recommended Posts

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 by Stéphane Wierzbicki
Added date output example

Share this post


Link to post
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.

Share this post


Link to post

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

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×