2 Replies Latest reply: May 15, 2014 10:08 AM by guyporter _ RSS

    Date Format Problem

    guyporter _

      I work in the Uk on  a US designed computer system. I have reports with a UK date format of DDMMYY that I need to change to a US format of MMDDYY to enable the exported file to be uploaded.

      Can anyone help me with the required formula to do this??

      (Please):)

        • Date Format Problem
          Data Kruncher

          Hi Guy. Welcome to the forum!

           

          So long as the system that will upload your exported file can (will?) convert a character field that looks like a date into a real date, then I may have a solution.

           

          First, go to the Options->Input dialog and set the date format to D/M/Y.

           

          For my test, I used this as the report:

          181210

          200610

          250110

          /codeIn the Table, these were presented as:

          MyDate

          12/18/2010

          6/20/10

          1/25/10[/code]I then built a Character calculated field with:

          Trim(Str(Month(MyDate),2,0,"0"))+

          Trim(Str(Day(MyDate),2,0,"0"))+

          Right(Trim(Str(Year(MyDate))),2)[/code]This generated the string that looks like a date in the format you want:

          ExportDate

          121810

          062010

          012510

          /codeKeep in mind that my computer's regional settings are for the US date formats, therefore the m/d/yy format presented in my Table window.

           

          Does that work for you?

            • Date Format Problem
              guyporter _

              Thanks for the post regarding this problem. I'm afraid we could not get the solution to work with this report. As a result I managed to get a local expert to come up with the following:-

              substr(dtoc(mydate),4,2)left(dtoc(mydate),2)substr(dtoc(mydate),9,2)

               

              Problem solved for now - onto the next!