8 Replies Latest reply on Apr 27, 2017 7:35 PM by 3208479

    Converting String to Date

    3208479

      I would like to convert the following string value below to a date.

       

      REPLACE(CAST(MONTH("Personal Information"."Date of Birth") AS CHAR)||'/'||CAST(DAY("Personal Information"."Date of Birth") AS CHAR)||'/'||CAST(YEAR(CURRENT_DATE) AS CHAR),' ','')

       

      Thanks in advance

        • 1. Re: Converting String to Date
          Sherry George

          Hi,

           

          Could you tell us what your requirement is ? Are you trying to calculate current age or something ? Also please provide sample values for the columns used and their data type.

          • 2. Re: Converting String to Date
            Gianni Ceresa

            +1 Sherry George: 3208479 if you want to convert a string to a date at least you could post the string you talk about ...

             

            Your formula seems to find the date of the birthday in the current year (so if you were born the 15 June 1980 your formula would return 15 June 2017).

            And as Sherry George said, what are you trying to do exactly? Because there is probably a simpler/shorter way to get to that point than using a weird formula to generate a string, transform it back in a date and then use it for some calculations.

             

            To transform a varchar into a date there is a formula: TO_DATETIME

            Syntax

            TO_DATETIME('string1', 'DateTime_formatting_string')

            Where:

            string1 is the string literal you want to convert

            DateTime_formatting_string is the DateTime format you want to use, such as

            yyyy.mm.dd hh:mi:ss. For this argument, yyyy represents year, mm represents month,

            dd represents day, hh represents hour, mi represents minutes, and ss represents

            seconds.

            • 3. Re: Converting String to Date
              3208479

              Hello Sherry,

               

              I am creating a report that will show all the people in a company that have upcoming birthdays in the next 30 days. The idea was to get extract the month and the day from the birthday and then append the year with the current year. I could then do a comparison where MYDATE between CURRENT_DATE and CURRENT_DATE + 30.

              The birthday is in the format MM/DD/YYYY. MYDATE should be in the same format but I was having trouble converting the string below into the date format.

               

              REPLACE(CAST(MONTH("Personal Information"."Date of Birth") AS CHAR)||'/'||CAST(DAY("Personal Information"."Date of Birth") AS CHAR)||'/'||CAST(YEAR(CURRENT_DATE) AS CHAR),' ','')

              • 4. Re: Converting String to Date
                Sherry George

                Thank you for providing the details. Based on a quick try, will something like this work for you. It calculates the number of days between current date and and birthday. You can replace Time.Date with Date of Birth.

                 

                TIMESTAMPDIFF(SQL_TSI_DAY, CURRENT_DATE, TIMESTAMPADD(SQL_TSI_YEAR,CAST( (TIMESTAMPDIFF(SQL_TSI_YEAR,"Time"."Date", CURRENT_DATE)) AS INT), "Time"."Date"))

                • 5. Re: Converting String to Date
                  Gianni Ceresa

                  Good try but it doesn't manage well the year change.

                  When you are in December in the next 30 days you must get people born in January and for now they are missing (because it will find the birtday of the current year instead of thinking at the next one).

                  It's a common issue when having to deal with that kind of things

                   

                  PS: I would say you do not need to make a CAST on the TIMESTAMPDIFF(SQL_TSI_YEAR ....) : it already return an INT only (timestampdiff will never give you anything else than integer numbers)

                  1 person found this helpful
                  • 6. Re: Converting String to Date
                    Sherry George

                    Good find, didn't think of that. The CAST, I probably did some messing up of the syntax when I tried without CAST (probably the brackets), anyway CAST is not needed.

                    • 7. Re: Converting String to Date
                      Sherry George

                      To address the concern for year change, I think you could find the difference between current date and next year birthday in a different column and then 'OR' the two columns in the filter. There might be a more elegant solution, but for what it's worth..

                      1 person found this helpful
                      • 8. Re: Converting String to Date
                        3208479

                        Thanks Sherry & Gianni. I'll try your recommendations and see if that works for me.