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

     1  
     2  -- test date format function
     3  drop table if exists t1;
     4  create table t1 (d datetime);
     5  
     6  insert into t1 values ('2004-07-14 08:19:30.333333'),('2005-08-15 22:50:59.999999');
     7  select date_format(d,'%a') from t1;
     8  select date_format(d,'%b') from t1;
     9  select date_format(d,'%c') from t1;
    10  select date_format(d,'%D') from t1;
    11  select date_format(d,'%d') from t1;
    12  select date_format(d,'%e') from t1;
    13  select date_format(d,'%f') from t1;
    14  select date_format(d,'%H') from t1;
    15  select date_format(d,'%h') from t1;
    16  select date_format(d,'%I') from t1;
    17  select date_format(d,'%i') from t1;
    18  select date_format(d,'%j') from t1;
    19  select date_format(d,'%k') from t1;
    20  select date_format(d,'%l') from t1;
    21  select date_format(d,'%M') from t1;
    22  select date_format(d,'%m') from t1;
    23  select date_format(d,'%p') from t1;
    24  select date_format(d,'%r') from t1;
    25  select date_format(d,'%S') from t1;
    26  select date_format(d,'%s') from t1;
    27  select date_format(d,'%T') from t1;
    28  select date_format(d,'%U') from t1;
    29  select date_format(d,'%u') from t1;
    30  select date_format(d,'%V') from t1;
    31  select date_format(d,'%v') from t1;
    32  select date_format(d,'%W') from t1;
    33  select date_format(d,'%w') from t1;
    34  select date_format(d,'%X') from t1;
    35  select date_format(d,'%x') from t1;
    36  select date_format(d,'%Y') from t1;
    37  select date_format(d,'%y') from t1;
    38  
    39  drop table t1;
    40  
    41  -- test select
    42  drop table if exists t2;
    43  create table t2 (f1 datetime);
    44  insert into t2 (f1) values ('2005-01-01');
    45  insert into t2 (f1) values ('2005-02-01');
    46  select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t2 order by date_format(f1, "%M");
    47  drop table t2;
    48  
    49  
    50  -- test insert
    51  drop table if exists t3;
    52  create table t3 (d date);
    53  insert into t3 values (date_format('20221214', '%Y-%m-%d'));
    54  drop table t3;
    55  
    56  
    57  -- test update,delete
    58  drop table if exists t4;
    59  create table t4 (a int, d date);
    60  insert into t4 values (1, '2002-02-20');
    61  update t4 set d=date_format('20221214', '%Y-%m-%d') where a=1;
    62  delete from t4 where d=date_format('20221214', '%Y-%m-%d');
    63  select * from t4;
    64  drop table t4;
    65  
    66  
    67  -- test operator
    68  drop table if exists t5;
    69  create table t5 (a int, b date);
    70  insert into t5 values
    71  (1,'2000-02-05'),(2,'2000-10-08'),(3,'2005-01-03'),(4,'2007-09-01'),(5,'2022-01-01');
    72  
    73  select * from t5 where b=date_format('20000205', '%Y-%m-%d');
    74  select * from t5 where b!=date_format('20000205', '%Y-%m-%d');
    75  select * from t5 where b<>date_format('20000205', '%Y-%m-%d');
    76  select * from t5 where b>date_format('20000205', '%Y-%m-%d');
    77  select * from t5 where b<date_format('20000205', '%Y-%m-%d');
    78  select * from t5 where b<=date_format('20000205', '%Y-%m-%d');
    79  select * from t5 where b between date_format('20000205', '%Y-%m-%d') and date_format('20220101', '%Y-%m-%d');
    80  select * from t5 where b not between date_format('20000205', '%Y-%m-%d') and date_format('20220101', '%Y-%m-%d');
    81  
    82  drop table t5;
    83  
    84  
    85  SELECT DATE_FORMAT("2009-01-01",'%W %d %M %Y') as valid_date;
    86  SELECT DATE_FORMAT('0000-01-01','%W %d %M %Y') as valid_date;
    87  SELECT DATE_FORMAT('0000-02-28','%W %d %M %Y') as valid_date;
    88  SELECT DATE_FORMAT('9999-02-28','%W %d %M %Y') as valid_date;
    89  
    90  select date_format('1997-01-02 03:04:05', '%M %W %D %Y %y %m %d %h %i %s %w');
    91  select date_format('1997-01-02', concat('%M %W %D','%Y %y %m %d %h %i %s %w'));
    92  
    93  select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
    94  select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');
    95  
    96  select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
    97  
    98  -- echo error
    99  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');
   100  
   101  
   102  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');
   103