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

[flipside]

Does the date portion format of your TimestampFormat match your data's format?

This should work, but breaks if the above mismatches.

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

flipside

EDIT: This would get round it ...

Date(Subfield([Case Created],' ',1)-7) as Date_LastSeven,