github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_todate.test (about)

     1  #SELECT different usage
     2  
     3  -- @bvt:issue#3203
     4  SELECT to_date('01,5,2013','%d,%m,%Y');
     5  SELECT to_date('May 1, 2013','%M %d,%Y');
     6  SELECT to_date('a09:30:17','a%h:%i:%s');
     7  SELECT to_date('a09:30:17','%h:%i:%s');
     8  SELECT to_date('09:30:17a','%h:%i:%s');
     9  SELECT to_date('abc','abc');
    10  SELECT to_date('9','%m');
    11  SELECT to_date('9','%s');
    12  SELECT to_date('00/00/0000', '%m/%d/%Y');
    13  SELECT to_date('04/31/2004', '%m/%d/%Y');
    14  SELECT to_date('00/00/0000', '%m/%d/%Y');
    15  SELECT to_date('200442 Monday', '%X%V %W');
    16  
    17  
    18  SELECT CAST(to_date('nope','%d-%m-%Y') AS YEAR);
    19  select to_date(concat_ws(' ','15-01-2001'), ' 2:59:58.999');
    20  -- @bvt:issue
    21  
    22  -- @bvt:issue#3203
    23  create table t1 (date char(30), format char(30) not null);
    24  insert into t1 values
    25  ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
    26  ('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
    27  ('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'),
    28  ('03-01-02 8:11:2.123456',   '%Y-%m-%d %H:%i:%S.%#'),
    29  ('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
    30  ('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
    31  ('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
    32  ('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'),
    33  ('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'),
    34  ('10:20:10', '%H:%i:%s'),
    35  ('10:20:10', '%h:%i:%s.%f'),
    36  ('10:20:10', '%T'),
    37  ('10:20:10AM', '%h:%i:%s%p'),
    38  ('10:20:10AM', '%r'),
    39  ('10:20:10.44AM', '%h:%i:%s.%f%p'),
    40  ('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'),
    41  ('15 September 2001', '%d %M %Y'),
    42  ('15 SEPTEMB 2001', '%d %M %Y'),
    43  ('15 MAY 2001', '%d %b %Y'),
    44  ('15th May 2001', '%D %b %Y'),
    45  ('Sunday 15 MAY 2001', '%W %d %b %Y'),
    46  ('Sund 15 MAY 2001', '%W %d %b %Y'),
    47  ('Tuesday 00 2002', '%W %U %Y'),
    48  ('Thursday 53 1998', '%W %u %Y'),
    49  ('Sunday 01 2001', '%W %v %x'),
    50  ('Tuesday 52 2001', '%W %V %X'),
    51  ('060 2004', '%j %Y'),
    52  ('4 53 1998', '%w %u %Y'),
    53  ('15-01-2001', '%d-%m-%Y %H:%i:%S'),
    54  ('15-01-20', '%d-%m-%y'),
    55  ('15-2001-1', '%d-%Y-%c');
    56  select date,format,to_date(date, format) as to_date from t1;
    57  select date,format,concat_ws(',',to_date(date, format)) as con from t1;
    58  select date,format,cast(to_date(date, format) as datetime) as datetime from t1;
    59  select date,format,DATE(to_date(date, format)) as date2 from t1;
    60  select concat_ws('',to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
    61  delete from t1;
    62  insert into t1 values
    63  ('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'),
    64  ('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'),
    65  ('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'),
    66  ('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'),
    67  ('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'),
    68  ('10:20:10AM', '%H:%i:%s%p'),
    69  ('15 Septembei 2001', '%d %M %Y'),
    70  ('15 Ju 2001', '%d %M %Y'),
    71  ('Sund 15 MA', '%W %d %b %Y'),
    72  ('Thursdai 12 1998', '%W %u %Y'),
    73  ('Sunday 01 2001', '%W %v %X'),
    74  ('Tuesday 52 2001', '%W %V %x'),
    75  ('Tuesday 52 2001', '%W %V %Y'),
    76  ('Tuesday 52 2001', '%W %u %x'),
    77  ('7 53 1998', '%w %u %Y');
    78  select date,format,to_date(date, format) as to_date from t1;
    79  select date,format,concat_ws(" ",to_date(date, format),'') as con from t1;
    80  drop table t1;
    81  -- @bvt:issue
    82  
    83  -- @bvt:issue#3203
    84  select to_date( 1, NULL );
    85  select to_date( NULL, 1 );
    86  select to_date( 1, IF(1=1,NULL,NULL) );
    87  
    88  select to_date('04 /30/2004', '%m /%d/%Y');
    89  select to_date('04/30 /2004', '%m /%d /%Y');
    90  select to_date('04/30/2004 ', '%m/%d/%Y ');
    91  SELECT DATE_SUB(to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
    92  SELECT DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE);
    93  select month(to_date(null, '%m')), month(to_date(null, '%m'));
    94  select to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
    95  select to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
    96  select to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
    97  select to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
    98  select to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
    99  select to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
   100  select to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
   101  select to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
   102  select to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
   103                                                  and '2007/10/20';
   104  select to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
   105                                                  and '2007/10/20 00:00:00 GMT';
   106  
   107  
   108  CREATE TABLE t1 ( a datetime(2) );
   109  CREATE TABLE t2 ( a timestamp(2) );
   110  SELECT to_date('09:22', '%H:%i');
   111  SELECT to_date('09:22:23.33', '%H:%i:%s.%f');
   112  INSERT INTO t1 select( to_date('09:22', '%H:%i') );
   113  INSERT INTO t1 select( to_date('09:22:23.33', '%H:%i:%s.%f') );
   114  select * from t1;
   115  DELETE FROM t1;
   116  INSERT INTO t1 select( to_date('2019-12-31', '%Y-%m-%d') );
   117  INSERT INTO t2 select( to_date('09:22', '%H:%i') );
   118  INSERT INTO t2 select( to_date('09:22:23.33', '%H:%i:%s.%f') );
   119  select * from t1;
   120  select * from t2;
   121  DELETE FROM t2;
   122  drop table t1;
   123  drop table t2;
   124  
   125  -- @bvt:issue