Date Conversion / Cleanup

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Date Conversion / Cleanup

Marc Pinnell-3
I need to clean up a "char" column of dates that are entered in just  
about every numerical format under the sun. Being that this is a  
"freebie" for an organization I volunteer for, I thought I would ask  
the group if anyone has something already written to convert dates  
like those below into a standard MySQL date - before I spent the time  
to write something. I found a tag by Bil on TagSwap that is close,  
but doesn't handle the "delimiterless" dates. Any ideas?

06/24/06
08/19/2006
07/29/06
072697
07-21-01
072796
072696
080197
080197
080197
051995
08-26-2001
080495
08/13/05
080296
081697
061591
061591
072294
07-11-98
07-21-01
09/10/05
080594
080594
053196
07-21-01


============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Date Conversion / Cleanup

Fletcher Sandbeck
> I need to clean up a "char" column of dates that are entered in just
> about every numerical format under the sun. Being that this is a  
> "freebie" for an organization I volunteer for, I thought I would ask  
> the group if anyone has something already written to convert dates  
> like those below into a standard MySQL date - before I spent the time  
> to write something. I found a tag by Bil on TagSwap that is close,  
> but doesn't handle the "delimiterless" dates. Any ideas?
>

These can all be handled by the following code.  Strip out all the
dashes and slashes.  If the resulting string is 6 characters long then
it uses a 2-digit year.  If it is 8 characters long then it uses a
4-digit year.  Otherwise, return an error since the date couldn't be
parsed.

var: 'dates' = (array: '06/24/06', '08/19/2006', '07/29/06', '072697',
'07-21-01', '072796', '072696', '080197', '080197', '080197', '051995',
'08-26-2001', '080495', '08/13/05', '080296', '081697', '061591',
'061591', '072294', '07-11-98', '07-21-01', '09/10/05', '080594',
'080594', '053196', '07-21-01');

iterate: $dates, (var: 'date');
  var: 'temp' = (string_replaceregexp: $date, -find='\\D', -replace='');
  if: $temp->size == 6;
    (date: $temp, -format='%m%d%y')->(format: '%Q');
  else: $temp->size == 8;
    (date: $temp, -format='%m%d%Y')->(format: '%Q');
  else;
    'ERROR ' + $temp;
  /if;
  '<br />';
/iterate;

2006-06-24
2006-08-19
2006-07-29
1997-07-26
2001-07-21
1996-07-27
1996-07-26
1997-08-01
1997-08-01
1997-08-01
1995-05-19
2001-08-26
1995-08-04
2005-08-13
1996-08-02
1997-08-16
1991-06-15
1991-06-15
1994-07-22
1998-07-11
2001-07-21
2005-09-10
1994-08-05
1994-08-05
1996-05-31
2001-07-21

Hope this helps,

[fletcher]
--
Fletcher Sandbeck                         [hidden email]
Director of Product Development       http://www.lassostudio.com
OmniPilot Software, Inc.                http://www.omnipilot.com

============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Date Conversion / Cleanup

Doug Gentry
Also Bil Corry's lp tags on tag_swap could process the old dates and  
wrestle them back into the proper format.

See http://tagswap.net/lp_date_stringToDate

and note that there are a number of other lp_ tags needed to make  
this work. Follow on link on the page to:

http://tagswap.net/lp__library  and just download his entire library.  
If you have access to your LassoStartup folder you can plop them all  
in, restart Lasso, and then have a nice suite of functions.

...Doug Gentry

On Feb 22, 2007, at 3:56 PM, Fletcher Sandbeck wrote:

>> I need to clean up a "char" column of dates that are entered in just
>> about every numerical format under the sun. Being that this is a
>> "freebie" for an organization I volunteer for, I thought I would ask
>> the group if anyone has something already written to convert dates
>> like those below into a standard MySQL date - before I spent the time
>> to write something. I found a tag by Bil on TagSwap that is close,
>> but doesn't handle the "delimiterless" dates. Any ideas?
>>
>
> These can all be handled by the following code.  Strip out all the
> dashes and slashes.  If the resulting string is 6 characters long then
> it uses a 2-digit year.  If it is 8 characters long then it uses a
> 4-digit year.  Otherwise, return an error since the date couldn't be
> parsed.
>
> var: 'dates' = (array: '06/24/06', '08/19/2006', '07/29/06', '072697',
> '07-21-01', '072796', '072696', '080197', '080197', '080197',  
> '051995',
> '08-26-2001', '080495', '08/13/05', '080296', '081697', '061591',
> '061591', '072294', '07-11-98', '07-21-01', '09/10/05', '080594',
> '080594', '053196', '07-21-01');
>
> iterate: $dates, (var: 'date');
>   var: 'temp' = (string_replaceregexp: $date, -find='\\D', -
> replace='');
>   if: $temp->size == 6;
>     (date: $temp, -format='%m%d%y')->(format: '%Q');
>   else: $temp->size == 8;
>     (date: $temp, -format='%m%d%Y')->(format: '%Q');
>   else;
>     'ERROR ' + $temp;
>   /if;
>   '<br />';
> /iterate;
>
> 2006-06-24
> 2006-08-19
> 2006-07-29
> 1997-07-26
> 2001-07-21
> 1996-07-27
> 1996-07-26
> 1997-08-01
> 1997-08-01
> 1997-08-01
> 1995-05-19
> 2001-08-26
> 1995-08-04
> 2005-08-13
> 1996-08-02
> 1997-08-16
> 1991-06-15
> 1991-06-15
> 1994-07-22
> 1998-07-11
> 2001-07-21
> 2005-09-10
> 1994-08-05
> 1994-08-05
> 1996-05-31
> 2001-07-21
>
> Hope this helps,
>
> [fletcher]
> --
> Fletcher Sandbeck                         [hidden email]
> Director of Product Development       http://www.lassostudio.com
> OmniPilot Software, Inc.                http://www.omnipilot.com
>
> ============================================
> Attend the Lasso Summit
> March 2-4, 2007 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage



---
Doug Gentry
Dynapolis & Southern Oregon University
p:  541-261-8501 / Toll Free: 866-890-6013
[hidden email]
www.dynapolis.com



============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Date Conversion / Cleanup

Marc Pinnell-3
In reply to this post by Fletcher Sandbeck
Perfect! You da man! Thanks much.

Marc


On Feb 22, 2007, at 3:56 PM, Fletcher Sandbeck wrote:

>> I need to clean up a "char" column of dates that are entered in just
>> about every numerical format under the sun. Being that this is a
>> "freebie" for an organization I volunteer for, I thought I would ask
>> the group if anyone has something already written to convert dates
>> like those below into a standard MySQL date - before I spent the time
>> to write something. I found a tag by Bil on TagSwap that is close,
>> but doesn't handle the "delimiterless" dates. Any ideas?
>>
>
> These can all be handled by the following code.  Strip out all the
> dashes and slashes.  If the resulting string is 6 characters long then
> it uses a 2-digit year.  If it is 8 characters long then it uses a
> 4-digit year.  Otherwise, return an error since the date couldn't be
> parsed.
>
> var: 'dates' = (array: '06/24/06', '08/19/2006', '07/29/06', '072697',
> '07-21-01', '072796', '072696', '080197', '080197', '080197',  
> '051995',
> '08-26-2001', '080495', '08/13/05', '080296', '081697', '061591',
> '061591', '072294', '07-11-98', '07-21-01', '09/10/05', '080594',
> '080594', '053196', '07-21-01');
>
> iterate: $dates, (var: 'date');
>   var: 'temp' = (string_replaceregexp: $date, -find='\\D', -
> replace='');
>   if: $temp->size == 6;
>     (date: $temp, -format='%m%d%y')->(format: '%Q');
>   else: $temp->size == 8;
>     (date: $temp, -format='%m%d%Y')->(format: '%Q');
>   else;
>     'ERROR ' + $temp;
>   /if;
>   '<br />';
> /iterate;
>
> 2006-06-24
> 2006-08-19
> 2006-07-29
> 1997-07-26
> 2001-07-21
> 1996-07-27
> 1996-07-26
> 1997-08-01
> 1997-08-01
> 1997-08-01
> 1995-05-19
> 2001-08-26
> 1995-08-04
> 2005-08-13
> 1996-08-02
> 1997-08-16
> 1991-06-15
> 1991-06-15
> 1994-07-22
> 1998-07-11
> 2001-07-21
> 2005-09-10
> 1994-08-05
> 1994-08-05
> 1996-05-31
> 2001-07-21
>
> Hope this helps,
>
> [fletcher]
> --
> Fletcher Sandbeck                         [hidden email]
> Director of Product Development       http://www.lassostudio.com
> OmniPilot Software, Inc.                http://www.omnipilot.com
>
> ============================================
> Attend the Lasso Summit
> March 2-4, 2007 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage


============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Date Conversion / Cleanup

Cory Robin-3
In reply to this post by Marc Pinnell-3
This is a custom tag that has been worked on by a couple of
our developers...  I think there are several ways of doing
it and if anyone wants to improve it that would be great!


// Correct date custom tag
Define_Tag:'correct_date', -Required='input_date',
-Priority='Replace';
        if:((#input_date)!=null);
                if:((String_FindRegExp: #input_date,
-Find='..-..-....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='../..-....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='..-../....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='./.-....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='.-./....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='.-.-....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='../.-....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='..-./....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='..-.-....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='./..-....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='.-../....')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='.-..-....')!='array');
                        #input_date->(Replace: '-','/');
                                return: (date_format: (#input_date+' 12:00:00'),
-format='%Q');
                else:((String_FindRegExp: #input_date,
-Find='..../../..')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='....-../..')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='..../..-..')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='..../.-.')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='....-./.')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='...././.')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='..../.-..')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='....-./..')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='...././..')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='..../..-.')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='....-../.')!='array')
                        || ((String_FindRegExp: #input_date,
-Find='..../../.')!='array');
                        #input_date->(Replace: '/','-');
                        return: (date_format: (#input_date+' 12:00:00'),
-format='%Q');
                else:( (((String:#input_date)->Size)==8) &&    // in case
they enter date as mmddyyyy
                           (Integer:(String_Extract:#input_date,
-StartPosition=1, -EndPosition=2)>=1 &&
Integer:(String_Extract:#input_date, -StartPosition=1,
-EndPosition=2)<=12) &&
                           (Integer:(String_Extract:#input_date,
-StartPosition=3, -EndPosition=4)>=1 &&
Integer:(String_Extract:#input_date, -StartPosition=3,
-EndPosition=4)<=31) &&
                           (Integer:(String_Extract:#input_date,
-StartPosition=5, -EndPosition=8)>=1900 &&
Integer:(String_Extract:#input_date, -StartPosition=5,
-EndPosition=8)<=2200)
                          );
                           
                           Return:((String_Extract:#input_date,
-StartPosition=5,
-EndPosition=8)'-'(String_Extract:#input_date,
-StartPosition=1,
-EndPosition=2)'-'(String_Extract:#input_date,
-StartPosition=3, -EndPosition=4));
                else;
                        protect;
                                return: (date_format: (#input_date+' 12:00:00'),
-format='%Q');
                        /protect;
                /if;
        /if;
/define_tag;



--- Marc Pinnell <[hidden email]> wrote:

> I need to clean up a "char" column of dates that are
> entered in just  
> about every numerical format under the sun. Being that
> this is a  
> "freebie" for an organization I volunteer for, I thought
> I would ask  
> the group if anyone has something already written to
> convert dates  
> like those below into a standard MySQL date - before I
> spent the time  
> to write something. I found a tag by Bil on TagSwap that
> is close,  
> but doesn't handle the "delimiterless" dates. Any ideas?
>
> 06/24/06
> 08/19/2006
> 07/29/06
> 072697
> 07-21-01
> 072796
> 072696
> 080197
> 080197
> 080197
> 051995
> 08-26-2001
> 080495
> 08/13/05
> 080296
> 081697
> 061591
> 061591
> 072294
> 07-11-98
> 07-21-01
> 09/10/05
> 080594
> 080594
> 053196
> 07-21-01

============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Date Conversion / Cleanup

Marc Pinnell-3
cool. thanks!

On Feb 22, 2007, at 9:24 PM, Cory Robin wrote:

> This is a custom tag that has been worked on by a couple of
> our developers...  I think there are several ways of doing
> it and if anyone wants to improve it that would be great!
>
>
> // Correct date custom tag
> Define_Tag:'correct_date', -Required='input_date',
> -Priority='Replace';
> if:((#input_date)!=null);
> if:((String_FindRegExp: #input_date,
> -Find='..-..-....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='../..-....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='..-../....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='./.-....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='.-./....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='.-.-....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='../.-....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='..-./....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='..-.-....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='./..-....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='.-../....')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='.-..-....')!='array');
> #input_date->(Replace: '-','/');
> return: (date_format: (#input_date+' 12:00:00'),
> -format='%Q');
> else:((String_FindRegExp: #input_date,
> -Find='..../../..')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='....-../..')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='..../..-..')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='..../.-.')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='....-./.')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='...././.')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='..../.-..')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='....-./..')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='...././..')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='..../..-.')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='....-../.')!='array')
> || ((String_FindRegExp: #input_date,
> -Find='..../../.')!='array');
> #input_date->(Replace: '/','-');
> return: (date_format: (#input_date+' 12:00:00'),
> -format='%Q');
> else:( (((String:#input_date)->Size)==8) &&    // in case
> they enter date as mmddyyyy
>   (Integer:(String_Extract:#input_date,
> -StartPosition=1, -EndPosition=2)>=1 &&
> Integer:(String_Extract:#input_date, -StartPosition=1,
> -EndPosition=2)<=12) &&
>   (Integer:(String_Extract:#input_date,
> -StartPosition=3, -EndPosition=4)>=1 &&
> Integer:(String_Extract:#input_date, -StartPosition=3,
> -EndPosition=4)<=31) &&
>   (Integer:(String_Extract:#input_date,
> -StartPosition=5, -EndPosition=8)>=1900 &&
> Integer:(String_Extract:#input_date, -StartPosition=5,
> -EndPosition=8)<=2200)
>  );
>
>   Return:((String_Extract:#input_date,
> -StartPosition=5,
> -EndPosition=8)'-'(String_Extract:#input_date,
> -StartPosition=1,
> -EndPosition=2)'-'(String_Extract:#input_date,
> -StartPosition=3, -EndPosition=4));
> else;
> protect;
> return: (date_format: (#input_date+' 12:00:00'),
> -format='%Q');
> /protect;
> /if;
> /if;
> /define_tag;
>
>
>
> --- Marc Pinnell <[hidden email]> wrote:
>
>> I need to clean up a "char" column of dates that are
>> entered in just
>> about every numerical format under the sun. Being that
>> this is a
>> "freebie" for an organization I volunteer for, I thought
>> I would ask
>> the group if anyone has something already written to
>> convert dates
>> like those below into a standard MySQL date - before I
>> spent the time
>> to write something. I found a tag by Bil on TagSwap that
>> is close,
>> but doesn't handle the "delimiterless" dates. Any ideas?
>>
>> 06/24/06
>> 08/19/2006
>> 07/29/06
>> 072697
>> 07-21-01
>> 072796
>> 072696
>> 080197
>> 080197
>> 080197
>> 051995
>> 08-26-2001
>> 080495
>> 08/13/05
>> 080296
>> 081697
>> 061591
>> 061591
>> 072294
>> 07-11-98
>> 07-21-01
>> 09/10/05
>> 080594
>> 080594
>> 053196
>> 07-21-01
>
> ============================================
> Attend the Lasso Summit
> March 2-4, 2007 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage


============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Date Conversion / Cleanup

Bil Corry-3
In reply to this post by Cory Robin-3
Cory Robin wrote on 2/22/2007 9:24 PM:
> This is a custom tag that has been worked on by a couple of
> our developers...  I think there are several ways of doing
> it and if anyone wants to improve it that would be great!

Also check out [lp_date_stringToDate], it breaks the datestring into pieces, then tries to figure out what each piece is (day, month, year).  Here are some strings it will correctly convert to the date type:

        'january 13, 2005',
        '13-JAN-05',
        '10/4',
        '3/2005/16',
        '16/2005/3',
        '11/12/8',
        '11/12/9',
        '11/12/10',
        '11/12/48',
        '11/12/49',
        '11/12/50',
        '2/29',
        '2/15/11',
        '2/15/32',
        '2/15/41',
        '15 März 2005',
        'lùglio 15 6',
        '2/10/2006 18:30:45',
        '6/10/2006 18:30:45',
        '2/10/2006 18:30:45 GMT',
        '6/10/2006 18:30:45 UTC',
        '2/10/2006 18:30:45 -0800',
        '6/10/2006 18:30:45 -0800',
        '2/10/2006 18:30:45 +0800',
        '6/10/2006 18:30:45 +0800',
        '4/2001/2002',
        '4/7-10/2005'


The ctag is here:

        <http://tagswap.net/lp_date_stringToDate>

However, it relies a lot of other ctags, so if you want to use it, you'll save yourself 20 minutes by just downloading the entire library of ctags at once:

        <http://tagswap.net/lp__library>


- Bil




============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Date Conversion / Cleanup

Bil Corry-3
Bil Corry wrote on 2/23/2007 8:55 AM:
> Here are some strings it will correctly convert to the date type:
>
>     '4/2001/2002',
>     '4/7-10/2005'

Doh!  Those last two are purposely ambiguous.  It won't convert those :)


- Bil



============================================
Attend the Lasso Summit
March 2-4, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage