Date format wierd

[userid128223]

my source date format looks like this.

19981203    (YYYYMMDD)

however when i bring that date and try passing thru date function, i get weird numbers.

year(saledate)as year,

I get numbers that is unlike year.

56606

Can someone please explain what is going on.

thanks

[swuehl]

QV don't recognize your date format, so it takes the number 19981203 as number of days since 1899-12-30.

And 19981203 is a day in year 56606.

To help QV with your date format, read the date in as

LOAD *, year(saledate) as year;

LOAD

date#(saledate,'YYYYMMDD') as saledate

...

FROM ...;

or change the standard date format at the top of your script accordingly.

Hope this helps,

Stefan

[userid128223]

Thanks

I tried doing something like this but get error. FIELD NOT FOUND "INVOICE DATE"

LOAD "SALEDATE",

Date( Date#( SALEDATE, 'YYYYMMDD'), 'YYYY-MM-DD') as INVOICE_DATE,

month(INVOICE_DATE)as month;

year(INVOICE_DATE) as year;

SQL SELECT "SALEDATE"

FROM.........................................

[swuehl · Accepted solution]

Yes, in the context of your LOAD, the input field INVOICE_DATE is not known (used in your month and year functions). That's why I used another preceding LOAD (where the field of the first load is known then). Note that even if it's called a preceding load, it's actually a following load (preceding because you write it before / in top of the second load, but the data flows from the SQL to your first LOAD to your second LOAD from bottom to top... Hm, maybe too confusing, right?

So either do

LOAD *,

month(INVOICE_DATE)as month;

year(INVOICE_DATE) as year;

LOAD "SALEDATE",

Date( Date#( SALEDATE, 'YYYYMMDD'), 'YYYY-MM-DD') as INVOICE_DATE;

SQL SELECT "SALEDATE"

FROM.........................................

or

LOAD "SALEDATE",

Date( Date#( SALEDATE, 'YYYYMMDD'), 'YYYY-MM-DD') as INVOICE_DATE,

month(Date#( SALEDATE, 'YYYYMMDD')) as month;

year(Date#( SALEDATE, 'YYYYMMDD')) as year;

SQL SELECT "SALEDATE"

FROM.........................................

Hope this helps,

Stefan