topic Re: Date Data - Strip Unwanted Information in QlikView

Date Data - Strip Unwanted Information

jmonroe918 — Wed, 14 Oct 2015 15:16:40 GMT

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

Re: Date Data - Strip Unwanted Information

rubenmarin — Wed, 14 Oct 2015 15:36:10 GMT

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 ....

Re: Date Data - Strip Unwanted Information

crystles — Wed, 14 Oct 2015 17:41:54 GMT

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!

Re: Date Data - Strip Unwanted Information

Wed, 14 Oct 2015 17:47:20 GMT

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

Re: Date Data - Strip Unwanted Information

jmonroe918 — Wed, 14 Oct 2015 23:11:32 GMT

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

Re: Date Data - Strip Unwanted Information

jmonroe918 — Wed, 14 Oct 2015 23:15:08 GMT

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

Re: Date Data - Strip Unwanted Information

Fri, 16 Oct 2015 12:45:31 GMT

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]

Re: Date Data - Strip Unwanted Information

jmonroe918 — Fri, 16 Oct 2015 14:22:23 GMT

Tripti:

I did do it in the Load script.

Jeff