topic Re: Datetime in Resident Table won't convert to Date in QlikView

Datetime in Resident Table won't convert to Date

timsaddler — Tue, 21 Apr 2015 08:19:13 GMT

Hello all

Silly issue this but i have extracted data from sql server into qlikview

I re-extract this from the resident table but the floor function doesn't work.

I need to show just the date - without the time.

Load
"Ref",
Requisition,
    "Region_Web_Details",
    "Status_Requisition",
    "Start_Date_Requisition",
    "End_Date_Requisition",
    "Application_Closing_Date_Requisition",
Date(Floor(ClosingDatePear)) as ClosingDatePear
    "Publish_On_Web",
    "Intranet_Only"
    Resident Vacancies
    Where Intranet_Only = 'Yes'
    and Status_Requisition = 'Open'
    Order by Status_Requisition;

Many thanks in advance for your help

Re: Datetime in Resident Table won't convert to Date

jpenuliar — Tue, 21 Apr 2015 08:23:22 GMT

Hi Tim,

Have you tried:

Date(Floor(ClosingDatePear),'DD/MM/YYYY') as ClosingDatePear,

Re: Datetime in Resident Table won't convert to Date

timsaddler — Tue, 21 Apr 2015 08:29:47 GMT

Hi Jonathan

Yes i have tried that and it doesn't work for some reason.

Re: Datetime in Resident Table won't convert to Date

anbu1984 — Tue, 21 Apr 2015 08:33:05 GMT

What is set in the DateFormat variable? Do you have time in DateFormat variable?

SET DateFormat='M/D/YYYY';

Re: Datetime in Resident Table won't convert to Date

fvelascog72 — Tue, 21 Apr 2015 08:34:12 GMT

Try:

Left(ClosingDatePear),10) as ClosingDatePear,

Re: Datetime in Resident Table won't convert to Date

jpenuliar — Tue, 21 Apr 2015 08:38:54 GMT

Maybe a :

Date(Floor(Timestamp

Re: Datetime in Resident Table won't convert to Date

martyn_birzys — Tue, 21 Apr 2015 09:12:35 GMT

I just use this

Date(ClosingDatePear) as ClosingDatePear,

Re: Datetime in Resident Table won't convert to Date

timsaddler — Tue, 21 Apr 2015 11:46:08 GMT

Hi - I asked an impossible question.

The reason it dosen't work is because i concatenated a row of text to provide a google filter.

Hence Qlikview is confused by whether the column is text or date !

I had to go back to sql to sort it.

Thanks for all the help though !