Date format changes when using where clasue on non-date field

[nigel987]

Hi there,

I have a weird issue with my dates and cannot find the cause.

I load data from a qvd file in the following way:

Directory;

LOAD AdID,

     AdType,

     AdStatus,

     AdStartDate,

     AdEndDate

FROM

[..\QVD\Anzeigen.qvd]

(qvd);

In this case everything is fine. My dates are displayed in the following way: DD.MM.YYYY HH:MM:SS

But when I load the data together with a where clause on a field which is not a date field, the date fields are displayed as days sind Dec 1899.

Directory;

LOAD AdID,

     AdType,

     AdStatus,

     AdStartDate,

     AdEndDate

FROM

[..\QVD\Anzeigen.qvd]

(qvd)

WHERE AdStatus >= 50;

Does anyone know why this happens?

Appreciate any advise. Have a good day!

[MK_QSL · Accepted solution]

Directory;

LOAD AdID,

     AdType,

     AdStatus,

     TimeStamp(AdStartDate) as AdStartDate,

     TimeStamp(AdEndDate) as AdEndDate

FROM

[..\QVD\Anzeigen.qvd]

(qvd)

WHERE AdStatus >= 50;

[tresesco]

Perhaps, somehow it's being treated as string and then trying to convert to date. Try like:

LOAD AdID,

     AdType,

     AdStatus,

    Date(TimeStamp#(AdStartDate, 'DD.MM.YYYY HH:MM:SS')) as AdStartDate,

     Date(TimeStamp#(AdEndDate, 'DD.MM.YYYY HH:MM:SS')) as AdEndDate

FROM

[..\QVD\Anzeigen.qvd]

(qvd)

WHERE AdStatus >= 50;

[nigel987]

Thank you Manish for the fast response! But do you know the reason why this happens?

[MK_QSL]

No idea but could be as per what tresesco has replied...!

[nigel987]

Hi,

Manish's answer was already helpful, but I tried yours as well.

After I reload it, there seem to be no values in this field.

Directory;

LOAD AdID,

     AdType,

     AdStatus,

     Date(Timestamp#(AdCreated,'DD.MM.YYYY HH:MM:SS')) AS AdCreated,

     AdCreatedD

FROM

[..\QVD\Anzeigen.qvd]

(qvd)

WHERE AdStatus >= 50;

[tresesco]

Try making the second MM small, like

Date(Timestamp#(AdCreated,'DD.MM.YYYY HH:mm:SS')) AS AdCreated,

[MK_QSL]

Try

Date(Timestamp#(AdCreated,'DD.MM.YYYY hh:mm:ss')) as AdCreated

[nigel987]

Hi,

I already have what I want by using

TimeStamp(AdCreated) as AdCreated,

but nevertheless, if you are interested:

neither

Date(Timestamp#(AdCreated,'DD.MM.YYYY HH:mm:SS')) AS AdCreated

nor

Date(Timestamp#(AdCreated,'DD.MM.YYYY hh:mm:ss')) AS AdCreated

did work for me.

Thanks