Date Formats

[#1]

Hi,

I am having trouble trying to use two tables with two differnt date formats so that when I select "Apr" all the related data shows for Apr, etc. Here is what the data data looks like in the two tables:

Table1:

Apr 1 2009 1:25PM
Apr 1 2009 1:58PM
Apr 1 2009 2:14PM
Apr 1 2009 2:45AM
Apr 1 2009 3:09PM
Apr 1 2009 3:29PM
Apr 1 2009 4:20PM

Table2:

03/04/2009 21:00
02/04/2009 17:17
02/04/2009 17:16
03/04/2009 22:12
06/04/2009 21:40
06/04/2009 21:41

Any help would be much appreciated.

Thanks,

Terry.

[#2]

Youd think that QV would be joining on the Numeric value of date wouldnt you, but alas this doesnt seem to be the case. It is Attempting to use the Formatted Text and as you say they are different!

This one gets me every time ...

ColinR

[prieper]

You need to "tell" QV, how to interprete the data, like

DATE(DATE#(YourDate1, 'MMM D YYYY hh:mm tt')) AS Date
or
DATE(DATE#(YourDate2, 'DD/MM/YYYY hh:mm')) AS Date

HTH
Peter

[#4]

Hi Peter,

Thanks for the info it worked..kind of.

The problem is the date format is slightly different within the table. This works for dates 1 to 9, but does not get the dates 10 to 31:

DATE(DATE#(YourDate1, 'MMM D YYYY hh:mm tt')) AS Date

This gets the date 10 to 31:

DATE(DATE#(YourDate1, 'MMM DD YYYY hh:mm tt')) AS Date

Note the spacing and and the extra D.

Is there a way I can get both dates within one statement?

Thanks,

Terry

[#5]

not sure if you can do it with a single statement. but have you tried a seperate linking table in your load script.

Ie fields named FilterDate,Yourdate1,YourDate2 etc as a seperate table linked to each of your source tables.

I have mulitple QVDs storing historical data in multiple date formats as no standard was introduced and im considering adding the formats into my Calendar dimension as a solution

[#6]

I think this should work:

IF(mid(YourDate1,6,1)=' ',DATE#(YourDate1, 'MMM D YYYY hh:mm tt'),DATE#(YourDate1, 'MMM DD YYYY hh:mm tt')) as MyDate

Rgds,

Sébastien