Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

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

Partner
Partner

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

Creator II
Creator II

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

Creator II
Creator II

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]

Creator II
Creator II

Tripti:

I did do it in the Load script.

Jeff