Dealing with two dates

[anuradhaa]

I have below two dates and they are in two different tables, I want to use common date.

Than i use master calander as belwo,

[TempCalendar]:

LOAD DISTINCT

    DATE,   

  Year(DATE) as Year,

  Month(DATE) as Month,

  'Q' & Ceil(Month(DATE)/3) as AQuarter

  RESIDENT ABC_DATA;

join

LOAD DISTINCT

  DATE,

  Year(DATE) as Year,

  Month(DATE) as Month,

  'Q' & Ceil(Month(DATE)/3) as Quarter

RESIDENT Charges;

MainCalendar:

load DATE,

  Year,

  Month,

  Quarter

  Resident TempCalendar;

Here i remane ABC_DATE and XYZ_DATE when i lod that table.

But when i look at the table year,Month is missing in some recrds.

Please help

ABC_DATE	XYZ_DATE
5/30/2014 8:45	1/18/2014 23:50
5/30/2014 8:45	3/25/2014 3:25
5/30/2014 8:45	3/28/2014 10:11
5/30/2014 8:45	4/2/2014 13:46
5/30/2014 8:45	4/4/2014 9:45
5/30/2014 8:45	4/14/2014 10:04
5/30/2014 8:45	4/15/2014 3:00
5/30/2014 8:45	4/15/2014 6:20
5/30/2014 8:45	4/18/2014 8:49
5/30/2014 8:45	4/18/2014 21:05
5/30/2014 8:45	4/21/2014 13:46
5/30/2014 8:45	4/26/2014 6:25
5/30/2014 8:45	4/28/2014 10:30
5/30/2014 8:45	5/5/2014 9:57
5/30/2014 8:45	5/8/2014 1:20
5/30/2014 8:45	5/10/2014 1:20
5/30/2014 8:45	5/12/2014 10:37

[Anonymous]

i agree with marcus.

you could use this to change it to date and eliminate the time data portion unless it is essential (alternatively have another time only table)

2014-06-27 14:01 use date(timestamp#(DATE, 'YYYY-MM-DD hh:mm')

07-18-2014 07:10 use date(timestamp#(DATE, 'MM-DD-YYYY hh:mm')

[marcus_malinow]

This shouldn't be a problem if you use the ALT function to interpret your dates in the tables you first load the dates into.

You could use something like

Date(ALT(timestamp#(Date, 'YYYY-MM-DD hh:mm'),  timestamp(Date('MM-DD-YYYY  hh:mm'))