23 Replies Latest reply: May 15, 2014 10:07 AM by Grant Perkins RSS

    Convert Text Field into Date field assistance

    adonis _

      Hi All,

      long time no write, hope all is well, with you all.

       

      I have a field which is a text string, and want to convert the field into date format.  The data is in Day, Month, Year Format.

      please advise

      Thanks in advance.

       

      100510

      100510

      100510

      -

      110510

      130510

      110510

      120510

      110510

      130510

      120510

      120510

      50510

      50510

      50510

        • Convert Text Field into Date field assistance
          Olly Bond

          Hello Adonis,

           

          Assuming your field is called and is a string, then:

           

          ctod(str(val([Input);6;0;"0"))

           

          should do the trick. Check the help for options on CTOD as your regional settings might be looking for MDY instead of DMY. You can work around this using:

           

          ctod(

          substr(str(val([Input);6;0;"0");3;2)+

          substr(str(val([Input);6;0;"0");1;2)+

          substr(str(val([Input);6;0;"0");5;2)

          )

           

          HTH,

           

          Olly

          • Convert Text Field into Date field assistance
            Data Kruncher

            Adonis,

             

            You need to first make this into a consistently six character string to avoid "unexpected results", and then [URL="http://********************/functions/conquering-date-problems-in-monarch"]have Monarch convert that string into a date[/URL].

             

            Assuming that your field is named CDate, then this'll do that:

             

            CtoD(Right("000000"+CDate,6),"d/m/y")[/code]HTH.

             

            Edit: Sometimes I remember to use the extended Str parameters as Olly has used, sometimes Right()+ gets you to the same result. Cross posting twice in an hour...

              • Convert Text Field into Date field assistance
                Olly Bond

                Hello Adonis,

                 

                You could use Kruncher's trick, or rely on the Str() function, where the "0" tells it to stuff the string with leading zeroes to make it up to the length 6, the other 0 refers to it not having any decimals.

                 

                Best wishes,

                 

                Olly

                  • Convert Text Field into Date field assistance
                    adonis _

                    i was thinking of doing a right, middle and left functions then add them together in date format, the problem is how can i get the middle if the middle starts from the left?

                    any way to get it to be the 3 and 4thdigit from the right?

                     

                    i figured let me add all four digits and add an extra step to remove the first 2 i guess that miay work if not will let you know

                     

                     

                    okay that works, i can figure out the last 4 digits,

                    the problem is the first 1 or 2 digits,

                    how can i do a left function where if the total number is 5 digits only take the first 1 digit,

                    if it is 6 digits then take the first 2 digits?

                      • Convert Text Field into Date field assistance
                        adonis _

                        any way to create a count field first then an if statement in another field?

                         

                        first cound the digits in the date field if it is 5 or 6

                        when i do an if statement in a different column to get the day field, what would happen is if the count is 5 then take the first digit, else take the first 2 digits?

                          • Convert Text Field into Date field assistance
                            Data Kruncher

                            With the formulas supplied earlier you don't need to worry about whether you've got 5 or 6 characters; it'll just work.

                             

                            That's assuming that, based on your sample, the day numbers sometimes don't have leading zeros while the month numbers and the year numbers always do.

                             

                            This longer formula will give you the same end result:

                            If(Len(CDate)=6,CtoD(CDate,"d/m/y"),CtoD("0"+CDate,"d/m/y"))[/code]

                             

                            Similarly you could get just the day number as text with:

                            If(Len(CDate)=6,Left(CDate,2),Left(CDate,1))[/CODE]

                             

                            or as a number with:

                            Val(If(Len(CDate)=6,Left(CDate,2),Left(CDate,1)))[/CODE]

                             

                            or even with:

                             

                            Day(If(Len(CDate)=6,CtoD(CDate,"d/m/y"),CtoD("0"+CDate,"d/m/y")))[/CODE]

                              • Convert Text Field into Date field assistance
                                Grant Perkins

                                Just to augment the previous comprehensive responses ....

                                 

                                If your input data is intended to be in d/m/y format but your Windows Regional default expects m/d/y format you should be able to control this directly in Monarch, whether or not the Input Options setting for Dates being appropriately set at the model level, with a DATE type field using the formula.

                                 

                                ctod(str(Date_Input,6,0,"0"),"d/m/y")

                                /code

                                 

                                The result should be presented in your default Regional format as set in Windows.

                                 

                                The proviso is, of course, that any 5 digit fields are the result of some earlier transformation of the original date field to a numeric field during which the leading zero for the day number (in this case, with d/m/y format specified) was dropped.

                                 

                                If for some reason leading zeros for both day and month numbers had been dropped things might prove somewhat more challenging.

                                 

                                There must be a number of other less regularly used approaches for this  - any more suggestions out there? Maybe something utilising Julian Dates or UNIX time numbers?

                                 

                                 

                                Grant

                                /SIZE

                                  • Convert Text Field into Date field assistance
                                    adonis _

                                    Data like always thanks for the feedback

                                     

                                    how do i point it to the field in question?

                                     

                                    the field name that has either 5 or 6 digits as you mention is called settle_date

                                     

                                    please let me know

                                     

                                     

                                    Grant

                                    the info is in d/m/y i want it in m/d/y or else i could do simply in monarch by making it a date field however due to the placement of data if i make it a date, it whacks everything out, the date is no longer accurate

                                     

                                    i also don't want to mess with any dates in the regional settings as i have some stuff working with batch files to apply a date parameter or current date to the end of he file, which i would hate that altered.

                                     

                                    please let me know if you know how to make the first 1 or 2 digits come out

                                    initially i was thinking creating 3 fields (seperate) for the year, month and day, and then adding them together with a /

                                    but now i am stuck because of the first 1 or 2 digits is not constant.

                                     

                                    Another way would be maybe if i can create a count of the word, in a seperate field and see whether or not if it is 5 then take 1 if it is 6 then take first 2.

                                    let me know

                          • Convert Text Field into Date field assistance
                            elginreigner _

                             

                            CtoD(Right("000000"+CDate,6),"d/m/y")[/code]HTH.

                             

                            /QUOTE

                             

                            This code example from Data will work perfectly. All you have to do is change the field to your field and specify the date output as in the example ('d/m/y').

                             

                            This covers missing leading zeros, i.e. 5 digit dates.

                              • Convert Text Field into Date field assistance
                                adonis _

                                thanks for the help but the thing is i need to include the settle_date field, that doesn't work without it

                                  • Convert Text Field into Date field assistance
                                    adonis _

                                    i think it is a miracle

                                    figured it out on my own

                                     

                                    fyi for you guys the process that works best is as follows:

                                     

                                    IF(Len()= 5,left(,1), left(,2))

                                      • Convert Text Field into Date field assistance
                                        adonis _

                                        good news is i can seperate the parts, now i am trying to figure out how to concatenate the 3 text fields together and to insert / in between both the month and day,  and day and year

                                          • Convert Text Field into Date field assistance
                                            adonis _

                                            in theory trying to fix the following

                                             

                                            Concatenate_XL(,/,[SD Day],/,[SD Year])

                                             

                                             

                                            but the adding of slashes is not working, anyone have any ideas how to bring the data together into a date format now?

                                              • Convert Text Field into Date field assistance
                                                Grant Perkins

                                                Assuming your Settle_date field is a CHARACTER field as extracted (if not, make it CHARACTER):

                                                 

                                                ctod(str(,6,0,"0"),"d/m/y")

                                                /CODE

                                                 

                                                Make the new CALCULATED field a DATE field. Use the above formula. It should present itself as MM/DD/YY after calcualtion if that is how your date format is set for your Windows Region default setting.

                                                 

                                                Forget all the slicing and dicing stuff, no matter how much fun it is. From what you have described you don't seem to need it here.

                                                 

                                                 

                                                Grant

                                                  • Convert Text Field into Date field assistance
                                                    adonis _

                                                    i tried using the formula has some operand error message

                                                     

                                                     

                                                    any way just to concatanate the data together and have a / in between?

                                                    i can do without the / but get errors when i try adding the slash

                                                      • Convert Text Field into Date field assistance
                                                        Grant Perkins

                                                        i tried using the formula has some operand error message

                                                         

                                                         

                                                        any way just to concatanate the data together and have a / in between?

                                                        i can do without the / but get errors when i try adding the slash[/quote]

                                                         

                                                        Apologies, my mistake, let's try that again. I used two methods and offered the wronng extract field type for the one I settled on. This should work.

                                                         

                                                        Assuming your Settle_date field is a [I]NUMERIC[/I][/B] field as extracted (if not, make it [I][U]NUMERIC[/U][/I][/B]):

                                                         

                                                         

                                                        ctod(str(,6,0,"0"),"d/m/y")

                                                        /code

                                                         

                                                        Make the new CALCULATED field a DATE field. Use the above formula. It should present itself as MM/DD/YY after calculation if that is how your date format is set for your Windows Region default setting.

                                                         

                                                        Forget all the slicing and dicing stuff, no matter how much fun it is. From what you have described you don't seem to need it here.

                                                         

                                                         

                                                        Grant

                                                          • Convert Text Field into Date field assistance
                                                            adonis _

                                                            Grant

                                                            it is a text field, it is not allowing me to use that formula

                                                            it comes back with invalid term error message

                                                              • Convert Text Field into Date field assistance
                                                                Grant Perkins

                                                                Grant

                                                                it is a text field, it is not allowing me to use that formula

                                                                it comes back with invalid term error message[/quote]

                                                                 

                                                                Make Settle Date NUMERIC.

                                                                 

                                                                STR will then convert that to a character type and add the missing leading zero (0).

                                                                 

                                                                CtoD (CHARACTER to DATE) will then convert the Character String produced by STR into a date format. You will have tols CtoD that the date yuo are feeding it is in d/m/y order. CtoD will output the DATE, assuming you have made the calculated field a DATE FIELD with Short date format, in your local default style. I assume that is MM/DD/YY (or m/d/y here to reduce the number of characters required in the formula.)

                                                                 

                                                                In case you have some other strange content in the extracted data you might also try this slightly extended formula that helps to deal with things like the dashes, making them NULLS.

                                                                 

                                                                ctod(str(Date_Input,6,0,"0"),"d/m/y","d")

                                                                /CODE

                                                                 

                                                                Notice the extra comma and "d" on the end.

                                                                 

                                                                See what that gives you.

                                                                 

                                                                 

                                                                Grant

                                                                • Convert Text Field into Date field assistance
                                                                  adonis _

                                                                  haha you are a genius Grant

                                                                   

                                                                  ctod(str(Date_Input,6,0,"0"),"d/m/y","d")

                                                                   

                                                                  worked lovely once i changed it to numeric

                                                                    • Convert Text Field into Date field assistance
                                                                      Grant Perkins

                                                                      haha you are a genius Grant

                                                                       

                                                                      ctod(str(Date_Input,6,0,"0"),"d/m/y","d")

                                                                       

                                                                      worked lovely once i changed it to numeric[/quote]

                                                                       

                                                                      Two of the most useful functions for manipulating things quite easily in Monarch are STR() and VAL(). Spending time getting to know them is worthwhile as they are keys for unlocking a lot of doors.

                                                                       

                                                                      You don't have to get them right first time every time (I don't as you can tell) but feeling comfortable in their company means you always have a way forward for a data format challenge.

                                                                       

                                                                       

                                                                      Grant.