11 Replies Latest reply: Mar 27, 2012 3:34 PM by Steve Sommers RSS

    Odd Date Formatting

    rdaley72

      Hi All,

       

      I have to integrate with an old system that formats dates like this (today): 32712 (yeah...really).  Does anyone have any suggestions on how to format a date like that?  I could just pull the date apart based on the positions in the string but sometimes it is going to have two numbers for the month and day and sometimes it will not (March 1 would be 3112 and November 12 will be 111212).  . 

       

      For the most part, I am asking because I am curious.  I assume the developer for that system will be able to reformat the date into something that makes sense but she is out of town for the next few days. 

       

      Thanks!

        • 1. Re: Odd Date Formatting
          Owain North Community Member

          I'm not sure that could ever work - how would you know whether 11112 was the first of November of the 11th January? Or even 11th November, 2002?

          • 2. Re: Odd Date Formatting
            Adam Cameron. Community Member

            I have to integrate with an old system that formats dates

             

            If only there was a function to format dates... format dates... date formatting... hmmm.

             

             

             

             

            Oh wait.  There is. dateFormat():

            http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f f4.html

             

            A list of CF functions (glance over it, don't try to remember 'em all, but remember the sorts of things one can do):

            http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec1a60c-7f fc.html

             

             

            Owain is, of course, right in what he says though.  This is a lousy approach to formatting date info.  Why can't the date be stored as a date, or in like some ISO format or similar?

             

            --

            Adam

            • 3. Re: Odd Date Formatting
              rdaley72 Community Member

              I was thinking that maybe I could start with the year first (assuming I won't be working here in the year 2100) but you are right, there is no way to tell.  I hadn't even though that the year could be less than two digits because the data will be later than 3/1 of this year.

               

              Maybe I can compare it to a second date for reference.  It is a shopping cart and these are return dates so if I looked at the sales date, I could probably narrow it down a bit.  I think that I will just wait for the developer of the other system to fix it.

               

              Thanks for your help!

              • 4. Re: Odd Date Formatting
                Owain North Community Member

                I think that I will just wait for the developer of the other system to fix it.

                 

                If the developer who decided that was a good date format has any sense, they've probably fled the country

                 

                Seriously, don't waste any time on it; it's a stupid format that simply cannot portray the data it's meant to. It's like saying "I've decided to only store the first ten characters of email addresses, that'll save on storage". As Adam says, there are ISO formats for a reason

                • 5. Re: Odd Date Formatting
                  rdaley72 Community Member

                  Yes Adam, I know about DateFormat and other functions, and I do have the important ones memorized.  I thought maybe somebody had a scripting technique but there is no way to tell as Owain pointed out.

                  • 6. Re: Odd Date Formatting
                    Adam Cameron. Community Member

                     

                    Yes Adam, I know about DateFormat and other functions, and I do have the important ones memorized.  I thought maybe somebody had a scripting technique but there is no way to tell as Owain pointed out.

                     

                    Of course it's possible.  Owain didn't say it's not possible, he said it was daft.  You asked this:

                     

                    I have to integrate with an old system that formats dates like this (today): 32712 (yeah...really).  Does anyone have any suggestions on how to format a date like that?

                     

                    stupidDateFormatNumber = dateFormat(someDateVariable, "mddyy");

                     

                    That's it.

                     

                    --

                    Adam

                    • 7. Re: Odd Date Formatting
                      rdaley72 Community Member

                      I'm not sure what you are talking about Adam.  I wasn't looking to create dates in the format.  I wanted to translate dates from another source to come in this format.  It most definitely would have been impossible in the format of my original post.  As Owain wrote, how do you know if 11112 is January 11 or November 1?   

                       

                      I figured it out.  It turns out the date will always be 6 characters but when I am pulling from the other system, the leading 0 is removed.  I made it work like this. 

                       

                      <cfset fulldate = #NumberFormat(thestupiddate,'000000')#>

                                                <cfset datemonth = #Left(fulldate,2)#>

                                  <cfset dateday = #Mid(fulldate,3,2)#>

                                  <cfset dateyear = #Right(fulldate,2)#>

                                  <cfset createddate = #CreateDate(dateyear, datemonth, dateday)#>

                                  #DateFormat(createddate,"mm/dd/yyyy")#

                       

                       

                      Thanks guys for your help, although Adam, you might want to take it easy on the sarcasm...just sayin'

                      • 8. Re: Odd Date Formatting
                        Adam Cameron. Community Member

                         

                        I'm not sure what you are talking about Adam.  I wasn't looking to create dates in the format.

                         

                        That is exactly what you asked though, wasn't it: "Does anyone have any suggestions on how to format a date like that?"  That's what you asked.  And what I provided details how to do that.

                         

                        However I get you meant to be asking "how to convert from that back into a date".  But you've got there, so that's good.

                         

                         

                        It most definitely would have been impossible in the format of my original post.  As Owain wrote, how do you know if 11112 is January 11 or November 1?  

                         

                         

                        No it wouldn't've, given the info you had to hand.  The right most are yy, the two preceding that are dd, and whatever is left (whether it's one or two chars) are the month part.  Once you know that - which you do already - you know how to do the conversion.

                         

                        What Owain seemed to be meaning is - by way of example - if you didn't already know the pattern (which, remember, you do), 11112 is ambiguous.  But given you do already know the pattern, it's not ambiguous:  it's 11 Jan 2012.  1 Nov 2012 would be 110112.

                         

                        That's not to say it's not a rubbish way of going about things.

                         

                         

                        The level of sarcasm was entirely appropriate, I think, given the question you actually asked (which, face it, didn't demonstrate a great deal of investigation, which is what I was trying to get across).  You can't blame me that you didn't word your question very well.  However I concede that give you weren't wanting to know what you were actually asking, that it - the tone - was misplaced.  And I apologise.

                         

                        --

                        Adam

                        • 9. Re: Odd Date Formatting
                          Owain North Community Member

                          And we're all friends again! Except from the developer who came up with that format and passed it as a numeric.

                          • 10. Re: Odd Date Formatting
                            rdaley72 Community Member

                            Indeed.  I have a hard and fast rule about not arguing with people i do not know over the internet.  I appreciate the help from both of you.

                             

                            It turns out that the 6 number date format is standard on the AS400, the offending system in question.  they can also provide the century number.  It is a pretty advanced system....if it were 1989.

                            • 11. Re: Odd Date Formatting
                              Steve Sommers Community Member

                              My guess is that the initial zero is missing because it is being stored in an integer variable meaning that the day portion is always zero padded. In a regular expression: (\d{1,2})(\d{2})(\d{2}). You could use a regular expression to parse it, count digits from the right starting with the year, or simply create a single if statement for the two possible lengths:

                               

                              <cfif len(dumbdate) EQ 5>

                              <cfset m=mid(dumbdate,1,1) />

                              <cfset d=mid(dumbdate,2,2) />

                              <cfset y=mid(dumbdate,4,2) />

                              <cfelseif len(dumbdate) EQ 6>

                              <cfset m=mid(dumbdate,1,2) />

                              <cfset d=mid(dumbdate,3,2) />

                              <cfset y=mid(dumbdate,5,2) />

                              <cfelse>

                              <cfabort showerror="ARGH! Date dumber than I thought!!!" />

                              </cfif>

                               

                              BTW, this brings back memories. I have seen some strange ways to squeeze every bit of data out of a byte over the years. Dates tended to muck up data structures the most. Then mix in big Indian vs. little Indian, etc., etc... Another thread.