Transferring a text value into date when loading in the data

[#1]

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?

[Anonymous]

you have a timestamp AS FORMAT

use:

date(floor(date#('4-1-2016 18:13:23','DD-MM-YYYY hh:mm:ss')))

[marcus_sommer]

Try this:

date(floor(timestamp#('4-1-2016 18:13:23', 'DD-MM-YYYY hh:mm:ss')), 'DDMMYYYY')

- Marcus

[sunny_talwar]

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";

[#5]

Guys, thanks for your input but neither of the suggestions work. I still get blank values... Any thoughts on what else I can do??

[sunny_talwar · Accepted solution]

How about this:

Date(SubField(OFF_SWITCH_DAT, ' ', 1)) as Date;

[Anonymous]

try this

Date(Date#(4-1-2016 18:13:23,'DD-MM-YYYY hh:mm:ss'),'DDMMYYYY')

[#8]

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);

[Anonymous]

Hope this works:

Date(Date#('4-1-2016 18:13:23','DD-MM-YYYY hh:mm:ss'),'DDMMYYYY')

[#10]

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