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: 
jmonroe918
Creator II
Creator II

Date Data - Strip Unwanted Information

I am loading data with date fields that have the following format: mm/dd/yyyy hh:mm:ss PM PDT

(example 10/14/2015 11:44:13 AM PDT).

I only want the month, day and year. What is the easiest way to do that?

Thanks.

Jeff

1 Solution

Accepted Solutions
rubenmarin

Hi Jeff,you can do:

Date(Date#(Left(DateField, 10), 'mm/dd/yyyy')) //returns the date in the date format of the QV document

From there in example for year you can do:

Year(Date#(Left(DateField, 10), 'mm/dd/yyyy'))

Or using preceding load:

LOAD Date, year(Date) as Year, month(Date) as Month, Day(Date) as Day;

LOAD Date(Date#(Left(DateField, 10), 'mm/dd/yyyy')) as Date

FROM ....

View solution in original post

7 Replies
rubenmarin

Hi Jeff,you can do:

Date(Date#(Left(DateField, 10), 'mm/dd/yyyy')) //returns the date in the date format of the QV document

From there in example for year you can do:

Year(Date#(Left(DateField, 10), 'mm/dd/yyyy'))

Or using preceding load:

LOAD Date, year(Date) as Year, month(Date) as Month, Day(Date) as Day;

LOAD Date(Date#(Left(DateField, 10), 'mm/dd/yyyy')) as Date

FROM ....

crystles
Partner - Creator III
Partner - Creator III

The easiest way would just be to put Date() around the field in the load script

Date(FieldName) as FieldName

The function defaults to mm/dd/yy format.

Hope this helps!

Not applicable

SET DateFormat='MM/DD/YYYY';

Use Date(Floor(Datefieldname)) in you what to do in qvw

It would be more efficient to do it in sql from where you are extracting the data. You can use convert() function

jmonroe918
Creator II
Creator II
Author

I'm sorry, but I selected incorrectly for "Correct Answer". The correct answer was Rubens (however I did have to change my setting to "MM/DD/YYYY" which Tripti Gupta stated (so I can get 01/01/2015 vs 1/1/2015).

Jeff

jmonroe918
Creator II
Creator II
Author

Tripti

When I used your code, I did not get any data to return. Only blanks (no error occurred)

     Date(Floor([Date Originated])) as [Date Originated2],

Jeff

Not applicable

Jeff, where are you trying the expression. Use it in Load Script.

Load
  Date(Floor([Date Originated])) as [Date Originated2] from xx.qvd

Then in your chart you can directly use [Date Originated2]

jmonroe918
Creator II
Creator II
Author

Tripti:

I did do it in the Load script.

Jeff