Date Data - Strip Unwanted Information

[jmonroe918]

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

[rubenmarin · Accepted solution]

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]

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!

[#4]

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]

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]

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

[#7]

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]

Tripti:

I did do it in the Load script.

Jeff