Date Format Issue

[brijesh1991]

Hi All,

I had a table:

ID,  Date1,  Date2

1, 22/10/2013,  15/09/2013

2, 15/12/2013,   17/10/2012

I want to take difference between Date1 And Date2.

Because of Date format of Date1 and Date2 is mixed with Date format and text format.

So I am not getting difference between them.

Is there anyway to convert the text format into Date format, so that I can take the difference between both dates?

All your suggestions will be welcomed!

Brijesh

[swuehl]

Use the date interpretation function date#() with an appropriate format code, like

Date#(FIELD, 'MM/DD/YYYY') as DateField,

[brijesh1991]

Thanks Swuehl,

When I am taking difference on front end(in a text box) as you said:

Date#(Date1,'DD/MM/YYYY')-Date#(Date2,'DD/MM/YYYY') it's coming fine if I will select a particular ID.

But I want the Difference at script level: i did following scripting, but not getting result.

MainTAB:

Load ID,Date#(Date1,'DD/MM/YYYY') as Date1, Date#(Date2,'DD/MM/YYYY') as Date2 from table1.xls;

NewTAB:

Load ID, Date1, Date2,

Date#(Date1,'DD/MM/YYYY')-Date#(Date1,'DD/MM/YYYY') as DateDiff Resident MainTAB;

drop table MainTAB;

Is there any problem into script?

[#4]

hi

Interval(Date(Date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') - Date(Date#(Date2,'DD/MM/YYYY'),'DD/MM/YYYY'))

[swuehl]

Date#(Date1,'DD/MM/YYYY')-Date#(Date1,'DD/MM/YYYY') as DateDiff Resident MainTAB;

Here you are subtracting Date1 field from Date1 field, which will result in zero.

You already interpreted your date values before, so

MainTAB:

LOAD ID,

     Date1,

     Date2,

     Date1- Date2 as DateDiff;

Load ID,Date#(Date1,'DD/MM/YYYY') as Date1, Date#(Date2,'DD/MM/YYYY') as Date2 from table1.xls;

should do.