topic Re: Date format changes when using where clasue on non-date field in QlikView

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

nigel987 — Mon, 07 Sep 2015 09:53:26 GMT

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!

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

MK_QSL — Mon, 07 Sep 2015 10:03:29 GMT

Directory;

LOAD AdID,

AdType,

AdStatus,

TimeStamp(AdStartDate) as AdStartDate,

TimeStamp(AdEndDate) as AdEndDate

FROM

[..\QVD\Anzeigen.qvd]

(qvd)

WHERE AdStatus >= 50;

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

tresB — Mon, 07 Sep 2015 10:06:16 GMT

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;

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

nigel987 — Mon, 07 Sep 2015 10:10:27 GMT

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

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

MK_QSL — Mon, 07 Sep 2015 10:13:16 GMT

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

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

nigel987 — Mon, 07 Sep 2015 10:17:45 GMT

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;

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

tresB — Mon, 07 Sep 2015 10:27:09 GMT

Try making the second MM small, like

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

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

MK_QSL — Mon, 07 Sep 2015 10:28:47 GMT

Try

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

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

nigel987 — Mon, 07 Sep 2015 11:18:59 GMT

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