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: 
umashankarus
Contributor III
Contributor III

Date conversion in load

Hi Experts

I have a date column

examples

a) 12/3/2015 1:01:22 PM

b) 6/8/2016  2:52:33 AM

I want to convert this to date format like

a) 12/03/2015

b) 06/08/2016

Date conversion works in a straight table (column 4 - refer image)

but not during data load (column 3 - ITEM_CREATE_DATE - refer image)

Statement in the data load is same as that used as dimension in the straight table

What is incorrect in the data load script that causes date conversion to be null ?

Thanks

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Anand is right that you don't need a parsing function like Date#() if Qlik automatically read in in as dual value (which you can see for example looking at the default right alignment of your timestamp values in the table).

Get the Dates Right

But I assume you not only want to reformat the timestamp values using Date() function, you want also to get rid of the numerical part that represents the time value, right? (If you don't get rid of the time part, you will see duplicate date values e.g. in a list box or dimension).

If you want to get rid of the time part, either use

LOAD

     Dayname(CREATION_DATE) as New_CREATION_DATE,

     ...

(if your default format is MM/DD/YYYY)

or

LOAD

     Date(Floor(CREATION_DATE),'MM/DD/YYYY') as New_CREATION_DATE,

     ...

View solution in original post

9 Replies
umashankarus
Contributor III
Contributor III
Author

Script:

LOAD

ID,

CREATION_DATE,

,Date(Date#(CREATION_DATE,'MM/DD/YYYY h:mm:ss ttt'),'MM/DD/YYYY')

Resident tablex;

antoniotiman
Master III
Master III

,Date(Date#(CREATION_DATE,'MM/DD/YYYY h:mm:ss ttt'),'MM/DD/YYYY') as New_CREATION_DATE

its_anandrjs

Try only with

LOAD

ID,

CREATION_DATE,

Date(CREATION_DATE,'MM/DD/YYYY') as New_CREATION_DATE

Resident tablex;

swuehl
MVP
MVP

Anand is right that you don't need a parsing function like Date#() if Qlik automatically read in in as dual value (which you can see for example looking at the default right alignment of your timestamp values in the table).

Get the Dates Right

But I assume you not only want to reformat the timestamp values using Date() function, you want also to get rid of the numerical part that represents the time value, right? (If you don't get rid of the time part, you will see duplicate date values e.g. in a list box or dimension).

If you want to get rid of the time part, either use

LOAD

     Dayname(CREATION_DATE) as New_CREATION_DATE,

     ...

(if your default format is MM/DD/YYYY)

or

LOAD

     Date(Floor(CREATION_DATE),'MM/DD/YYYY') as New_CREATION_DATE,

     ...

umashankarus
Contributor III
Contributor III
Author

Yes, I needed the time part to be removed

And your suggestion helped that

Thanks for the help

umashankarus
Contributor III
Contributor III
Author

Thanks for the input

umashankarus
Contributor III
Contributor III
Author

Thanks for the input

its_anandrjs

Hi,

What about me i am Wrong on input no Correct / Helpfull etc

Just Kidding

Regards,
Anand

umashankarus
Contributor III
Contributor III
Author

Anand, It was helpful

But it displayed displayed duplicate values, since time component was remaining ...which was corrected when Floor() function was used

Again Thanks for the response and help

Regards,

Umashankar