Date formats

[Willo]

hi;

i noticed what for me is a strange behavior, but i dont know if this come from mysql, delphi or mydac.

i have this sentence:

Select re_fven,(case when SUBSTRING(RE_NUM,1,1) = '1' then DATE_ADD(RE_FVEN,INTERVAL 15 DAY) else DATE_ADD(RE_FVEN,INTERVAL -15 DAY) end) as fechavencido from recibos

and after that, i want to append those dates into a VirtualTable, again on 2 different datetime fields, like this

Code: Select all

VirtualT.Append;
vdate1.asdatetime := Q1.Fieldbyname('re_fven').asdatetime;
vdate2.asdatetime := Q1.Fieldbyname('fechavencido').asdatetime;
VirtualT.Post;

but it didnt work like that... i got "invalid date" errors, so i changed the code like this:

VirtualT.Append;
xfecha := Qcartas.FieldByname('re_fven').Asstring;
vdate1.asdatetime := EncodeDate(StrToInt(copy(xfecha,7,4)), StrToInt(copy(xfecha,4,2)),
StrToInt(copy(xfecha,1,2)));

xfecha := Qcartas.FieldByname('fechavencido').Asstring;
vdate2.asdatetime := EncodeDate(StrToInt(copy(xfecha,1,4)),
StrToInt(copy(xfecha,6,2)),
StrToInt(copy(xfecha,9,2)));
VirtualT.Post;

Please note that original table field (RE_FVEN) is returned with different date format that calculated field (fechavencido).

why is that?

[Antaeus]

The CASE operator converts DATETIME values to strings, and applies the DATETIME format of MySQL. To solve this problem you can either change the datetime_format server parameter, or perform an explisit cast to DATETIME:

Code: Select all

  cast((case when SUBSTRING(RE_NUM,1,1) = '1'   
             then DATE_ADD(RE_FVEN,INTERVAL 15 DAY)   
             else DATE_ADD(RE_FVEN,INTERVAL -15 DAY) end) 
       as datetime) as fechavencido1