Converting String to Date

[#1]

I have a column in my spread sheet that is in YYYY-MM format and this is a dimension in a bar chart. It is in a string format but how can I convert it to a date format?

I have below format in the data load editor

DateFormat='MM/DD/YYYY';

Thank you!

[JonnyPoole]

you can use the Date and Date# function.

First use Date# which reads a string and interpets as a date:

Date#( [FieldName],'YYYY-MM')  as  NewDate

Then you can change the date format display using Date()  . Wrap it around the Date# so the result of Date# flows right into the Date function as follows:

Date(     Date#( [FieldName],'YYYY-MM')      ,  'MM/DD/YYYY'    )    as NewDate

[sunny_talwar]

There are couple of ways you can do this:

1) Date#(DateField, 'YYYY-MM') as DateField

2) MakeDate(SubField(DateField, '-', 1), SubField(DateField, '-', 2), 1) as DateField

HTH

Best,

Sunny

[sasiparupudi1]

hi

Basically you need a string that contains all the three information required for a date ie Year , Month and Day otherwise,by using the following conversion ,

Date(Date#([Your Date Field],'YYYY-MM') , 'MM/DD/YYYY') is always going to use 1 as the day for your dates

ex:

2015-01 will be 01/01/2015

2015-02 will be 02/01/2015

so on..

hth

Sasi

[#5]

Thank you all for your help!

I have one more question about the date format.

My chart has month/year on the axis but it only shows the months that have corresponding amount. Is it possible to format so that it shows other months that do not have any data? Or would I have to create months on the spreadsheet manually?