Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

timsaddler
Contributor II

Datetime in Resident Table won't convert to Date

Hello all

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

Datetime.jpg

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

7 Replies
jpenuliar
Valued Contributor III

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

Hi Tim,

Have you tried:

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

timsaddler
Contributor II

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

Hi Jonathan

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

anbu1984
Honored Contributor III

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

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

SET DateFormat='M/D/YYYY';

fvelascog72
Valued Contributor

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

Try:

Left(ClosingDatePear),10) as ClosingDatePear,

jpenuliar
Valued Contributor III

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

Maybe a :

Date(Floor(Timestamp

martyn_birzys
Contributor

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

I just use this

Date(ClosingDatePear) as ClosingDatePear,

timsaddler
Contributor II

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

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 !

Community Browser