extracting date

[#1]

Hi All,

I have a query mentioned below:

LOAD

  [Case Created]         as AsOfDttm,

  Date#(Subfield([Case Created],'/',2)) as AsOfDttm_Day,

Date#([Case Created]) - 7  as Date_LastSeven,

  '7daysback' as PreviousCurrentFlag;

LOAD DISTINCT [Case Created]

RESIDENT Report;

This should return 4 columns,

1. AsOfDttm

2. AsOfDttm_Day

3. Date_LastSeven - it should return 7 days before's date

4. PreviousCurrentFlag

Getting all the values except the 3rd one.

Can anyone please help me on this..

Thanks in advance

[#2]

TRY THIS

Date#([Case Created],'DD-MM-YYYY') - 7  as Date_LastSeven

HERE BOLD PART IS THE FORMAT OF YOUR DATE FIELD THAT IS [Case Created]

YOU CAN SET THE BOLD PART ACCORDING TO YOUR FORMAT OF FIELD

FORMAT OF DATE--

DATE(DATE#(FIELDNAME,'DD/MM-YYYY'),'DD-MM-YYYY')

[#3]

TRY THIS

NUM(Date([Case Created])) - 7  as Date_LastSeven

OR THIS ONE

DATE(NUM(Date([Case Created])) - 7)  as Date_LastSeven

[jagan]

Hi,

Try like this

LOAD

  [Case Created]         as AsOfDttm,

  Date#(Subfield([Case Created],'/',2)) as AsOfDttm_Day,

Date(Date#([Case Created], 'MM/DD/YYYY') - 7)  as Date_LastSeven,

  '7daysback' as PreviousCurrentFlag;

LOAD DISTINCT [Case Created]

RESIDENT Report;

In the above script change the format of the date to your date format.

Hope this helps you.

Regards,

Jagan.

[#5]

non of them worked fokes

[#6]

non of them worked fokes

[#7]

can u show the original data?

[#8]

it is in string

in the format MM/DD/YYYY hh:mm:ss

[#9]

already tried.. dint work

[#10]

TRY THIS

TRY THIS

LOAD

  [Case Created]         as AsOfDttm,

  Date#(Subfield([Case Created],'/',2)) as AsOfDttm_Day,

Date(Num(Date(date#([Case Created],'MM/DD/YYYY hh:mm:ss'),'DD-MM-YYYY')) - 7)  as Date_LastSeven,

  '7daysback' as PreviousCurrentFlag;

LOAD DISTINCT [Case Created]

RESIDENT Report;