github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_str_to_date.sql (about)

     1  drop table if exists t1;
     2  create table t1(
     3  cdate varchar(20),
     4  ctime varchar(20),
     5  cdatetime varchar(20)
     6  );
     7  insert into t1 values('04/31/2004','09:30:17', '2022-05-27 11:30:00');
     8  insert into t1 values('05/31/2012','11:30:17', '2012-05-26 12:30:00');
     9  insert into t1 values('04/23/2009','01:30:17', '2002-07-26 02:30:01');
    10  insert into t1 values('01/31/2004','12:30:17', '2001-03-26 08:10:01');
    11  insert into t1 values('07/03/2018','05:30:17', '2011-08-26 07:15:01');
    12  insert into t1 values('08/25/2014','09:30:17', '2011-11-26 06:15:01');
    13  insert into t1 values('06/30/2022','04:30:17', '2011-12-26 06:15:01');
    14  SELECT cdate,STR_TO_DATE(cdate,'%m/%d/%Y') from t1;
    15  SELECT ctime,STR_TO_DATE(ctime,'%h:%i:%s')  from t1;
    16  SELECT cdatetime,STR_TO_DATE(cdatetime,'%Y-%m-%d %H:%i:%s') from t1;
    17  
    18  SELECT cdate,TO_DATE(cdate,'%m/%d/%Y') from t1;
    19  SELECT ctime,TO_DATE(ctime,'%h:%i:%s')  from t1;
    20  SELECT cdatetime,TO_DATE(cdatetime,'%Y-%m-%d %H:%i:%s') from t1;
    21  drop table t1;
    22  
    23  drop table if exists t2;
    24  create table t2(
    25  cdate varchar(20),
    26  ctime varchar(20),
    27  cdatetime varchar(30)
    28  );
    29  insert into t2 values('May 1, 2013','11:13:56','8:10:2.123456 13-01-02');
    30  insert into t2 values('Feb 28, 2022','12:33:51','12:19:2.123456 06-01-02');
    31  insert into t2 values('Jul 20, 2022','03:23:36','15:21:2.123456 22-01-02');
    32  insert into t2 values('Aug 1, 2013','01:43:46','11:11:2.123456 25-01-02');
    33  insert into t2 values('Nov 28, 2022','10:53:41','19:31:2.123456 11-01-02');
    34  insert into t2 values('Dec 20, 2022','09:23:46','1:41:2.123456 02-01-02');
    35  SELECT cdate,STR_TO_DATE(cdate,'%b %d,%Y') from t2;
    36  SELECT ctime,STR_TO_DATE(ctime,'%r')  from t2;
    37  SELECT cdatetime,STR_TO_DATE(cdatetime,'%H:%i:%S.%f %y-%m-%d') from t2;
    38  SELECT cdate,TO_DATE(cdate,'%b %d,%Y') from t2;
    39  SELECT ctime,TO_DATE(ctime,'%r')  from t2;
    40  SELECT cdatetime,TO_DATE(cdatetime,'%H:%i:%S.%f %y-%m-%d') from t2;
    41  
    42  drop table t2;
    43  
    44  SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
    45  SELECT str_to_date('May 1, 2013','%M %d,%Y');
    46  SELECT str_to_date('February 28, 2022','%M %d,%Y');
    47  select str_to_date('8:10:2.123456 13-01-02','%H:%i:%S.%f %y-%m-%d');
    48  select str_to_date('11:13:56', '%r');
    49  SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
    50  SELECT str_to_date('01,5,2013','%d,%m,%Y');
    51  SELECT str_to_date('May 1, 2013','%M %d,%Y');
    52  SELECT str_to_date('a09:30:17','a%h:%i:%s');
    53  SELECT str_to_date('a09:30:17','%h:%i:%s');
    54  SELECT str_to_date('09:30:17a','%h:%i:%s');
    55  SELECT str_to_date('abc','abc');
    56  SELECT str_to_date('9','%m');
    57  SELECT str_to_date('9','%s');
    58  SELECT str_to_date('00/00/0000', '%m/%d/%Y');
    59  SELECT str_to_date('04/31/2004', '%m/%d/%Y');
    60  SELECT str_to_date('00/00/0000', '%m/%d/%Y');
    61  SELECT str_to_date('200442 Monday', '%X%V %W');
    62  select str_to_date(concat_ws(' ','15-01-2001'), ' 2:59:58.999');
    63  select concat_ws('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
    64  select str_to_date('04 /30/2004', '%m /%d/%Y');
    65  select str_to_date('04/30 /2004', '%m /%d /%Y');
    66  select str_to_date('04/30/2004 ', '%m/%d/%Y ');
    67  SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
    68  SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
    69  SELECT str_to_date('09:22', '%H:%i');
    70  SELECT str_to_date('09:22:23.33', '%H:%i:%s.%f');
    71  SELECT str_to_date('09:22', '%H:%i');
    72  SELECT str_to_date('2008-01-01',replace(replace(replace(replace(replace(replace(replace('yyyy-MM-dd','a','%p'),'ss','%s'),'mm','%i'),'HH','%H'),'yyyy','%Y'),'dd','%d'),'MM','%m'));
    73  
    74  SELECT TO_DATE('04/31/2004', '%m/%d/%Y');
    75  SELECT to_date('May 1, 2013','%M %d,%Y');
    76  SELECT to_date('February 28, 2022','%M %d,%Y');
    77  select to_date('8:10:2.123456 13-01-02','%H:%i:%S.%f %y-%m-%d');
    78  select to_date('11:13:56', '%r');
    79  SELECT DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
    80  SELECT to_date('01,5,2013','%d,%m,%Y');
    81  SELECT to_date('May 1, 2013','%M %d,%Y');
    82  SELECT to_date('a09:30:17','a%h:%i:%s');
    83  SELECT to_date('a09:30:17','%h:%i:%s');
    84  SELECT to_date('09:30:17a','%h:%i:%s');
    85  SELECT to_date('abc','abc');
    86  SELECT to_date('9','%m');
    87  SELECT to_date('9','%s');
    88  SELECT to_date('00/00/0000', '%m/%d/%Y');
    89  SELECT to_date('04/31/2004', '%m/%d/%Y');
    90  SELECT to_date('00/00/0000', '%m/%d/%Y');
    91  SELECT to_date('200442 Monday', '%X%V %W');
    92  select to_date(concat_ws(' ','15-01-2001'), ' 2:59:58.999');
    93  select concat_ws('',to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
    94  select to_date('04 /30/2004', '%m /%d/%Y');
    95  select to_date('04/30 /2004', '%m /%d /%Y');
    96  select to_date('04/30/2004 ', '%m/%d/%Y ');
    97  SELECT DATE_SUB(to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
    98  SELECT DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
    99  SELECT to_date('09:22', '%H:%i');
   100  SELECT to_date('09:22:23.33', '%H:%i:%s.%f');
   101  SELECT to_date('09:22', '%H:%i');