3 Replies Latest reply: May 15, 2014 10:11 AM by Olly Bond RSS

    Reformatting date using CTOD

    AlexM _

      It should be straightforward but I'm trying to convert a date which is currently displaying in DD/MM/YYYY short date format to YYYY/MM/DD instead.

       

      My ignorance of the Ctod function - whether selected as a Date/Time or Character format - is not allowing me to proceed using the following:

       

      Ctod(,"y/m/d")

       

      Could someone please put me out of my misery? Thanks.

        • Reformatting date using CTOD
          Olly Bond

          Hi Alex,

           

          Try "dtoc" instead of "ctod".

           

          Sorry that was terse - I was trying to reply qickly from my mobile.

           

          Assume that is a field of Date type, perhaps of the format Short Date. How that shows on your computer is not configured by Monarch but is set in your Windows Regional Settings (usually configurable via the Control Panel). So your regional settings show short dates as DD/MM/YYYY. In order to show a date as YYYY/MM/DD you can:

           

          - either change your Regional Settings so that all short dates are shown in this format (that would affect all the work you do on your computer in Office, Monarch, whatever)

           

          - or fake it with a Character field that is made up from the original Date field. For this you need DtoC (date to character).

           

          I think that dtoc is literal so it will convert DD/MM/YYYY to "DD/MM/YYYY", but then it's simple to muck around with this:

           

          lsplit(dtoc();3;"/";3)"/"lsplit(dtoc();3;"/";2)"/"lsplit(dtoc();3;"/";1)  

           

          should give you what you want.

           

          Hope this helps,

           

          Olly