please explain

[userid128223]

I am confused please explain

SALES:

SQL SELECT

PhoneNumber,

CustNo,

InvoiceDate

FROM ............;

If I want to manipulate date and that to show up in SALES Tables how do i do it.

when i try this, gives me field not found error.

SALES:

SQL SELECT

PhoneNumber,

CustNo,

InvoiceDate

Date( Date#( InvoiceDate, 'YYYYMMDD'), 'YYYYMMDD') as tmpDate

FROM ............;

If I try this. It gives me my date but omits other field and doe not create SALES table, instead creates "table" with tmpDate

LOAD Date( Date#( InvoiceDate, 'YYYYMMDD'), 'YYYYMMDD') as tmpDate;

SALES:

SQL SELECT

PhoneNumber,

CustNo,

InvoiceDate

FROM ............;

How can i read date via SQL, manipulate date and show up in same table. Please help.

thanks

[swuehl · Accepted solution]

I think you are almost there, use a preceding load to be able to use QV functions, like in your last try:

SALES:

LOAD *,

Date( Date#( InvoiceDate, 'YYYYMMDD'), 'YYYYMMDD') as tmpDate;

SQL SELECT

PhoneNumber,

CustNo,

InvoiceDate

FROM ............;

or

SALES:

LOAD PhoneNumber, CustNo,

Date( Date#( InvoiceDate, 'YYYYMMDD'), 'YYYYMMDD') as InvoiceDate;

SQL SELECT

PhoneNumber,

CustNo,

InvoiceDate

FROM ............;

if you just want to format InvoiceDate without creating tmpDate.

Hope this helps,

Stefan

[#3]

You are missing a comma after InvoiceDate

InvoiceDate   <---

Date( Date#( InvoiceDate, 'YYYYMMDD'), 'YYYYMMDD') as tmpDate

Stephen