github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_date_format_1.result (about) 1 drop table if exists t1; 2 create table t1 (d datetime); 3 insert into t1 values ('2004-07-14 08:19:30.333333'),('2005-08-15 22:50:59.999999'); 4 select date_format(d,'%a') from t1; 5 date_format(d,'%a') 6 Wed 7 Mon 8 select date_format(d,'%b') from t1; 9 date_format(d,'%b') 10 Jul 11 Aug 12 select date_format(d,'%c') from t1; 13 date_format(d,'%c') 14 7 15 8 16 select date_format(d,'%D') from t1; 17 date_format(d,'%D') 18 14th 19 15th 20 select date_format(d,'%d') from t1; 21 date_format(d,'%d') 22 14 23 15 24 select date_format(d,'%e') from t1; 25 date_format(d,'%e') 26 14 27 15 28 select date_format(d,'%f') from t1; 29 date_format(d,'%f') 30 000000 31 000000 32 select date_format(d,'%H') from t1; 33 date_format(d,'%H') 34 08 35 22 36 select date_format(d,'%h') from t1; 37 date_format(d,'%h') 38 08 39 10 40 select date_format(d,'%I') from t1; 41 date_format(d,'%I') 42 08 43 10 44 select date_format(d,'%i') from t1; 45 date_format(d,'%i') 46 19 47 51 48 select date_format(d,'%j') from t1; 49 date_format(d,'%j') 50 196 51 227 52 select date_format(d,'%k') from t1; 53 date_format(d,'%k') 54 8 55 22 56 select date_format(d,'%l') from t1; 57 date_format(d,'%l') 58 8 59 10 60 select date_format(d,'%M') from t1; 61 date_format(d,'%M') 62 July 63 August 64 select date_format(d,'%m') from t1; 65 date_format(d,'%m') 66 07 67 08 68 select date_format(d,'%p') from t1; 69 date_format(d,'%p') 70 AM 71 PM 72 select date_format(d,'%r') from t1; 73 date_format(d,'%r') 74 08:19:30 AM 75 10:51:00 PM 76 select date_format(d,'%S') from t1; 77 date_format(d,'%S') 78 30 79 00 80 select date_format(d,'%s') from t1; 81 date_format(d,'%s') 82 30 83 00 84 select date_format(d,'%T') from t1; 85 date_format(d,'%T') 86 08:19:30 87 22:51:00 88 select date_format(d,'%U') from t1; 89 date_format(d,'%U') 90 28 91 33 92 select date_format(d,'%u') from t1; 93 date_format(d,'%u') 94 29 95 33 96 select date_format(d,'%V') from t1; 97 date_format(d,'%V') 98 28 99 33 100 select date_format(d,'%v') from t1; 101 date_format(d,'%v') 102 29 103 33 104 select date_format(d,'%W') from t1; 105 date_format(d,'%W') 106 Wednesday 107 Monday 108 select date_format(d,'%w') from t1; 109 date_format(d,'%w') 110 3 111 1 112 select date_format(d,'%X') from t1; 113 date_format(d,'%X') 114 2004 115 2005 116 select date_format(d,'%x') from t1; 117 date_format(d,'%x') 118 2004 119 2005 120 select date_format(d,'%Y') from t1; 121 date_format(d,'%Y') 122 2004 123 2005 124 select date_format(d,'%y') from t1; 125 date_format(d,'%y') 126 04 127 05 128 drop table t1; 129 drop table if exists t2; 130 create table t2 (f1 datetime); 131 insert into t2 (f1) values ('2005-01-01'); 132 insert into t2 (f1) values ('2005-02-01'); 133 select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t2 order by date_format(f1, "%M"); 134 d1 d2 135 02 February 136 01 January 137 drop table t2; 138 drop table if exists t3; 139 create table t3 (d date); 140 insert into t3 values (date_format('20221214', '%Y-%m-%d')); 141 drop table t3; 142 drop table if exists t4; 143 create table t4 (a int, d date); 144 insert into t4 values (1, '2002-02-20'); 145 update t4 set d=date_format('20221214', '%Y-%m-%d') where a=1; 146 delete from t4 where d=date_format('20221214', '%Y-%m-%d'); 147 select * from t4; 148 a d 149 drop table t4; 150 drop table if exists t5; 151 create table t5 (a int, b date); 152 insert into t5 values 153 (1,'2000-02-05'),(2,'2000-10-08'),(3,'2005-01-03'),(4,'2007-09-01'),(5,'2022-01-01'); 154 select * from t5 where b=date_format('20000205', '%Y-%m-%d'); 155 a b 156 1 2000-02-05 157 select * from t5 where b!=date_format('20000205', '%Y-%m-%d'); 158 a b 159 2 2000-10-08 160 3 2005-01-03 161 4 2007-09-01 162 5 2022-01-01 163 select * from t5 where b<>date_format('20000205', '%Y-%m-%d'); 164 a b 165 2 2000-10-08 166 3 2005-01-03 167 4 2007-09-01 168 5 2022-01-01 169 select * from t5 where b>date_format('20000205', '%Y-%m-%d'); 170 a b 171 2 2000-10-08 172 3 2005-01-03 173 4 2007-09-01 174 5 2022-01-01 175 select * from t5 where b<date_format('20000205', '%Y-%m-%d'); 176 a b 177 select * from t5 where b<=date_format('20000205', '%Y-%m-%d'); 178 a b 179 1 2000-02-05 180 select * from t5 where b between date_format('20000205', '%Y-%m-%d') and date_format('20220101', '%Y-%m-%d'); 181 a b 182 1 2000-02-05 183 2 2000-10-08 184 3 2005-01-03 185 4 2007-09-01 186 5 2022-01-01 187 select * from t5 where b not between date_format('20000205', '%Y-%m-%d') and date_format('20220101', '%Y-%m-%d'); 188 a b 189 drop table t5; 190 SELECT DATE_FORMAT("2009-01-01",'%W %d %M %Y') as valid_date; 191 valid_date 192 Thursday 01 January 2009 193 SELECT DATE_FORMAT('0000-01-01','%W %d %M %Y') as valid_date; 194 invalid input: invalid datatime value 0000-01-01 195 SELECT DATE_FORMAT('0000-02-28','%W %d %M %Y') as valid_date; 196 invalid input: invalid datatime value 0000-02-28 197 SELECT DATE_FORMAT('9999-02-28','%W %d %M %Y') as valid_date; 198 valid_date 199 Sunday 28 February 9999 200 select date_format('1997-01-02 03:04:05', '%M %W %D %Y %y %m %d %h %i %s %w'); 201 date_format('1997-01-02 03:04:05', '%M %W %D %Y %y %m %d %h %i %s %w') 202 January Thursday 2nd 1997 97 01 02 03 04 05 4 203 select date_format('1997-01-02', concat('%M %W %D','%Y %y %m %d %h %i %s %w')); 204 date_format('1997-01-02', concat('%M %W %D','%Y %y %m %d %h %i %s %w')) 205 January Thursday 2nd1997 97 01 02 12 00 00 4 206 select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v'); 207 date_format('1998-12-31','%x-%v') date_format('1999-01-01','%x-%v') 208 1998-53 1998-53 209 select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v'); 210 date_format('1999-12-31','%x-%v') date_format('2000-01-01','%x-%v') 211 1999-52 1999-52 212 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'); 213 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') 214 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15| January|Saturday|31st|1998|98|Sat|Jan|031|01|31|01|15|6 215 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'); 216 invalid argument function date_format, bad value [BIGINT VARCHAR] 217 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'); 218 invalid input: invalid datatime value 19980021000000