4 Replies Latest reply on Jan 24, 2013 3:39 PM by lpuis

    Extracting a date from a number

    lpuis

      Title

      Extracting a date from a number

      Post

           Hi all,

           I have a relative easy question I believe, but I just can't get the right way to do it.

           I have records containing a field that is a patient record number. It contains the reversed date of birth from the patient, eg. A720131DS00S, which means this patients' birthdate is 31/01/1972. How can I extract the birthdate and put it in the field 'Date of Birth' as a date?

           Many thanks,

           Luc

        • 1. Re: Extracting a date from a number
          raybaudi

                

               Try:
                
               Let([
               dt = Left ( Filter ( PatientNumber ; 1234567890 ) ; 6 ) ;
               m = Middle ( dt ; 3 ; 2 ) ;
               d = Middle ( dt ; 5 ; 2 ) ;
               y = Middle ( dt ; 1 ; 2 )
               ];
               date ( m ; d ; y + If ( y < 15 ; 2000 ; 1900 ) )
               )
                
               The patient must be younger than 98
          • 2. Re: Extracting a date from a number
            Sorbsbuster

                 (raybaudi is highlighting the limitation of your method of establishing the date of birth.  When you only use two-digit years you cannot determine if '08' is '1908' or '2008'.)

            • 3. Re: Extracting a date from a number
              lpuis

                   Thanks for the fast responses, fellows. I'll try it and let you know. Bit busy with lots of stuff, today.

                   Cheers,

                    

              • 4. Re: Extracting a date from a number
                lpuis

                     Hi guys,

                     @raybaudi, it simply works! Thanks a lot!

                     @Sorbsbuster, I've never had a patient older than 98, it's cardiac surgery and it would be very unlikely (oldest one ever was 92, never know in the future, of course). But thanks for the info, anyway!

                     Cheers to you, smart people (making other people smarter),

                     Luc