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

     1  create table t_dates(
     2  a_date date,
     3  b_datetime datetime,
     4  c_timestamp timestamp
     5  );
     6  
     7  insert into t_dates values('1999-04-05','1999-04-05 11:01:02','1999-04-05 11:01:02');
     8  insert into t_dates values('2004-04-03','2004-04-03 13:11:10','2004-04-03 13:11:10');
     9  insert into t_dates values('1999-04-05','1999-04-05 11:01:02','1999-04-05 11:01:02.123456');
    10  insert into t_dates values('2004-04-03','2004-04-03 13:11:10','2004-04-03 13:11:10.123456');
    11  insert into t_dates values('1999-04-05','1999-04-05 11:01:02.123456','1999-04-05 11:01:02.123456');
    12  insert into t_dates values('1999-04-05','2004-04-03 13:11:10.123456','2004-04-03 13:11:10.123456');
    13  
    14  
    15  #1.Various parameter type tests
    16  #date入参类型为date、datetime,timestamp
    17  select date_format(a_date, '%W %M %Y'),date_format(b_datetime, '%W %M %Y'),date_format(c_timestamp, '%W %M %Y') from t_dates;
    18  
    19  #The type of date parameter is null, or the type of format input parameter is null
    20  select date_format(NULL, '%W %M %Y'),date_format('1999-04-05 11:01:02', NULL),date_format(NULL, NULL);
    21  
    22  
    23  #Test with date input parameter type of string
    24  SELECT DATE_FORMAT('2012-12-21 23:12:34.123456', '%b %M %m %c %D %d %e %j %k %h %i %p %r %T %s %f %U %u %V %v %a %W %w %X %x %Y %y %%');
    25  
    26  #2.The first parameter of the date_format function is a constant test
    27  #The format parameter contains all format matches
    28  SELECT DATE_FORMAT('2010-01-07 23:12:34.12345', '%b %M %m %c %D %d %e %j %k %h %i %p %r %T %s %f %U %u %V %v %a %W %w %X %x %Y %y %%');
    29  
    30  #The format parameter contains all format matches
    31  SELECT DATE_FORMAT('0001-01-01 00:00:00.123456', '%b %M %m %c %D %d %e %j %k %h %i %p %r %T %s %f %U %u %V %v %a %W %w %X %x %Y %y %%');
    32  
    33  #The format parameter contains all format matching characters mixed with other characters
    34  SELECT DATE_FORMAT('2012-12-21 23:12:34.123456', 'abc%b %M %m %c %D %d %e %j %k %h %i %p %r %T %s %f %U %u %V %v %a %W %w %X %x %Y %y!123 %%xyz %z');
    35  
    36  #3.The first parameter of date_format function is table field batch test
    37  select date_format(a_date,'%Y-%m-%d %H:%i:%s'),date_format(b_datetime,'%Y-%m-%d %H:%i:%s'),date_format(c_timestamp,'%Y-%m-%d %H:%i:%s') from t_dates;
    38  
    39  #4.The first parameter of date_format function is expression batch test
    40  select date_format(date('2004-04-03 13:11:10'),'%Y-%m-%d %H:%i:%s'),date_format(cast('2004-04-03 13:11:10' as datetime),'%Y-%m-%d %H:%i:%s') from t_dates;
    41  
    42  drop table t_dates;
    43  SELECT DATE_FORMAT('123456', '%Y-%m-%d %H:%i:%s');
    44  SELECT DATE_FORMAT('2023-11-08 12:00', '%Y-%m-%d %H:%i:%s');
    45  SELECT DATE_FORMAT('2023-11-08 15:00:00', '%Y-%m-%d %H:%i:%s');
    46  SELECT DATE_FORMAT('2023-11-08 15:00:', '%Y-%m-%d %H:%i:%s');
    47  
    48  select date_format('19980021000000', '%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%W');
    49  select date_format('19980131131415', '%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%W');
    50  select date_format('19981321000000', '%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%W');
    51  select date_format('19981021000000', '%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%W');
    52  select date_format('19981033000000', '%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%W');
    53  select date_format('19981031000000', '%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%W');