4 Replies Latest reply: Sep 24, 2015 10:23 AM by Seona Joung RSS

    Converting String to Date

      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!

        • Re: Converting String to Date
          Jonathan Poole

          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

          • Re: Converting String to Date
            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

            • Re: Converting String to Date
              Sasidhar Parupudi

              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

              • Re: Converting String to Date

                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?