Date function issue

[#1]

Hi

I've a txt files with different number of fields and one of those field is CaptureDateTime(i.e in timestamp format)

For Eg:

CaptureDateTime

11-24-2011 10:30AM

10-23-2011 10:30AM

9-22-2011 10:30AM

8-21-2011 10:30AM

7-20-2011 10:30AM

12-1-2010 10:30AM

12-2-2010 10:30AM

11-25-2010 10:30AM

11-24-2010 10:30AM

11-23-2010 10:30AM

11-22-2009 10:30AM

11-22-2008 10:30AM

11-22-2007 10:30AM

11-22-2006 10:30AM

11-22-2005 10:30AM

11-22-2004 10:30AM

11-22-2003 10:30AM

11-22-2002 10:30AM

11-22-2001 10:30AM

11-22-2000 10:30AM

11-22-1999 10:30AM

11-22-1998 10:30AM

- I want to retrive data from present day to last 12 months data only for that i'm using the below code it is working exactly what i want.

LOAD *

FROM

[..\..\2_QvdGenerator\QVD\DateTable.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

Where Date#(CaptureDateTime,'MM/DD/YYYY') >= Date(AddMonths(Today(),-12),'MM-DD-YYYY')

;

- And i'm also trying to retrive the data below 12 months to last 7 years but it is not working..

LOAD *

FROM

[..\..\2_QvdGenerator\QVD\DateTable.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

Where Date(CaptureDateTime,'MM/DD/YYYY') >= AddMonths(Today()-86,'MM/DD/YYYY') and Date(CaptureDateTime,'MM/DD/YYYY') < AddMonths(Today(),12)

;

But it is not working can any one help me...

[swuehl]

I would go for a code along the lines that already work:

Where Date#(CaptureDateTime,'MM/DD/YYYY') >= Date(AddMonths(Today(),-7*12),'MM-DD-YYYY') and Date#(CaptureDateTime,'MM/DD/YYYY') < Date(AddMonths(Today(),-12),'MM-DD-YYYY')

Regards,

Stefan

edit: added minus

[#3]

Good idea but still it is not working...

For your reference i'm attaching sample file with text file also

[swuehl · Accepted solution]

It seems that your first load hasn't worked either, you need to use a different format code to parse the Date time in:

QUALIFY *;

RTQMain:

LOAD *

//,Date#(CaptureDateTime,'M-D-YYYY hh:mmTT') as Formatted

FROM

DateTable.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

Where Date#(CaptureDateTime,'M-D-YYYY hh:mmTT') >= Date(AddMonths(Today(),-12),'MM-DD-YYYY')

;

////STORE RTQMain into RTQMain.qvd;

////DROP Table RTQMain;

//

////************* HistoricalRTQMain Table for last seven years ************

HistoricalRTQMain:

LOAD *

//,Date#(CaptureDateTime,'M-D-YYYY hh:mmTT') as Formatted

FROM

DateTable.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

Where Date#(CaptureDateTime,'M-D-YYYY hh:mmTT') >= Date(AddMonths(Today(),-84),'MM-DD-YYYY') and Date#(CaptureDateTime,'M-D-YYYY hh:mmTT') < Date(AddMonths(Today(),-12),'MM-DD-YYYY')

;

Regards,

Stefan

[#5]

Thanks swuehl... it's working but why format code is playing major role here..

In first table it is fine.. But coming to the second table it is playing major role why?

and for 7 year's we need to use -86 but you have given me with -84 why?

[swuehl]

Well, in your original version you've missed 10-23-2011, haven't you?

So I think we also had a problem with the first load. Not sure why it returns results that seemed to be correct at first glance, though.

And I used -84 to indicate 7 years à 12 month back in time, is this not what you want? Then just set this value to whatever needed.

Hope this helps,

Stefan