Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transferring a text value into date when loading in the data

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?

1 Solution

Accepted Solutions
sunny_talwar

How about this:

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

View solution in original post

9 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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

sunny_talwar

How about this:

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

Anonymous
Not applicable
Author

try this

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

Not applicable
Author

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
Not applicable
Author

Hope this works:

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

Not applicable
Author


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