Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have got data that is structured like this:
Date
4-1-2016 18:13:23
As I would like to have the value as DDMMYYYY I try to load the data like this:
ODBC CONNECT32 TO [DWH_P3.world;DBQ=DWH_P3] (XUserId is BQYVFIBHWBMOTcAHbCTCEBdLTKREFLFJDKRGVOVD, XPassword is dUbMAYVMQbcSWYIGTBSB);
LOAD WEEK_IN,
Date#(OFF_SWITCH_DAT, 'DD-MM-YYYY');
SQL SELECT *
FROM "LAB_SERVICES"."FBI_LKR_OFF_UITVAL";
This however gives the following:
Date
42295
So the date format seems wrong. Any thoughts on what goes wrong here?
you have a timestamp AS FORMAT
use:
date(floor(date#('4-1-2016 18:13:23','DD-MM-YYYY hh:mm:ss')))
Try this:
date(floor(timestamp#('4-1-2016 18:13:23', 'DD-MM-YYYY hh:mm:ss')), 'DDMMYYYY')
- Marcus
Try this:
ODBC CONNECT32 TO [DWH_P3.world;DBQ=DWH_P3] (XUserId is BQYVFIBHWBMOTcAHbCTCEBdLTKREFLFJDKRGVOVD, XPassword is dUbMAYVMQbcSWYIGTBSB);
LOAD WEEK_IN,
Date(Floor(Date#(OFF_SWITCH_DAT, 'D-M-YYYY h:mm:ss'))) as Date;
SQL SELECT *
FROM "LAB_SERVICES"."FBI_LKR_OFF_UITVAL";
Guys, thanks for your input but neither of the suggestions work. I still get blank values... Any thoughts on what else I can do??
How about this:
Date(SubField(OFF_SWITCH_DAT, ' ', 1)) as Date;
try this
Date(Date#(4-1-2016 18:13:23,'DD-MM-YYYY hh:mm:ss'),'DDMMYYYY')
pleas check it
Tab1:
LOAD Date,
date(Date#(Date(Date),'DD-MM-YYYY'),'DDMMYYYY') as date
//date(floor(timestamp#('Date', 'DD-MM-YYYY hh:mm:ss')), 'DDMMYYYY') as date5
FROM
(ooxml, embedded labels, table is Sheet1);
Hope this works:
Date(Date#('4-1-2016 18:13:23','DD-MM-YYYY hh:mm:ss'),'DDMMYYYY')
Before trying this please maintain below format
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';
date(Date#(Date(Date),'DD-MM-YYYY'),'DDMMYYYY') as date
I have got result
Date date
01-04-2016 16:13:23 Pm 01042016