Date Formatting Problems.........

[#1]

Hi Everyone,

I have a quite simple problem that I cannot seem to solve.

I have numerous date fields across many tables in my data model.

The format of the date is as follows: YYYY-MM-DDThh:mm:ss

I was thinking maybe the 'T' in the middle is confusing things?

Qlikview doesn't seem to want to recognise this as a date.

Should I be setting this as the Timestamp Format?

I am wanting to be able to pull out Year, Month, Week, Day and build a Master Callendar.

Any help or tips on this would be much appreciated.

Thanks in advance!

[#2]

You can take only the date by using this code:

date(left('YYYY-MM-DDThh:mm:ss',10),'YYYY-MM-DD') as DateField

So the begining of your master calendar will be something like that:

Temp: 

Load 

               min(DateField) as minDate, 

               max(DateField) as maxDate 

Resident Fato_Final; 

Let varMinDate = Num(Date#(Peek('minDate', 0, 'Temp'),'YYYY-MM-DD')); 

Let varMaxDate = Num(Date#(Peek('maxDate', 0, 'Temp'),'YYYY-MM-DD')); 

DROP Table Temp; 

Hope that works

[#3]

Cheers for the tips!

[Anonymous]

I'd rather use timestamp#() instead of string functions, e,g,:

date(floor(timestamp#("Your Date Field", 'YYYY-MM-DDThh:mm:ss'))) as DateField