github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_date_format.result (about) 1 create table t_dates( 2 a_date date, 3 b_datetime datetime, 4 c_timestamp timestamp 5 ); 6 insert into t_dates values('1999-04-05','1999-04-05 11:01:02','1999-04-05 11:01:02'); 7 insert into t_dates values('2004-04-03','2004-04-03 13:11:10','2004-04-03 13:11:10'); 8 insert into t_dates values('1999-04-05','1999-04-05 11:01:02','1999-04-05 11:01:02.123456'); 9 insert into t_dates values('2004-04-03','2004-04-03 13:11:10','2004-04-03 13:11:10.123456'); 10 insert into t_dates values('1999-04-05','1999-04-05 11:01:02.123456','1999-04-05 11:01:02.123456'); 11 insert into t_dates values('1999-04-05','2004-04-03 13:11:10.123456','2004-04-03 13:11:10.123456'); 12 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; 13 date_format(a_date, '%W %M %Y') date_format(b_datetime, '%W %M %Y') date_format(c_timestamp, '%W %M %Y') 14 Monday April 1999 Monday April 1999 Monday April 1999 15 Saturday April 2004 Saturday April 2004 Saturday April 2004 16 Monday April 1999 Monday April 1999 Monday April 1999 17 Saturday April 2004 Saturday April 2004 Saturday April 2004 18 Monday April 1999 Monday April 1999 Monday April 1999 19 Monday April 1999 Saturday April 2004 Saturday April 2004 20 select date_format(NULL, '%W %M %Y'),date_format('1999-04-05 11:01:02', NULL),date_format(NULL, NULL); 21 date_format(NULL, '%W %M %Y') date_format('1999-04-05 11:01:02', NULL) date_format(NULL, NULL) 22 null null null 23 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 %%'); 24 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 Dec December 12 12 21st 21 21 356 23 11 12 PM 11:12:34 PM 23:12:34 34 123456 51 51 51 51 Fri Friday 5 2012 2012 2012 12 % 26 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 %%'); 27 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 %%') 28 Jan January 01 1 7th 07 7 007 23 11 12 PM 11:12:34 PM 23:12:34 34 123450 01 01 01 01 Thu Thursday 4 2010 2010 2010 10 % 29 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 %%'); 30 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 %%') 31 Jan January 01 1 1st 01 1 001 0 12 00 AM 12:00:00 AM 00:00:00 00 123456 00 01 53 01 Mon Monday 1 0000 0001 0001 01 % 32 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'); 33 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') 34 abcDec December 12 12 21st 21 21 356 23 11 12 PM 11:12:34 PM 23:12:34 34 123456 51 51 51 51 Fri Friday 5 2012 2012 2012 12!123 %xyz z 35 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; 36 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') 37 1999-04-05 00:00:00 1999-04-05 11:01:02 1999-04-05 11:01:02 38 2004-04-03 00:00:00 2004-04-03 13:11:10 2004-04-03 13:11:10 39 1999-04-05 00:00:00 1999-04-05 11:01:02 1999-04-05 11:01:02 40 2004-04-03 00:00:00 2004-04-03 13:11:10 2004-04-03 13:11:10 41 1999-04-05 00:00:00 1999-04-05 11:01:02 1999-04-05 11:01:02 42 1999-04-05 00:00:00 2004-04-03 13:11:10 2004-04-03 13:11:10 43 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; 44 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') 45 2004-04-03 00:00:00 2004-04-03 13:11:10 46 2004-04-03 00:00:00 2004-04-03 13:11:10 47 2004-04-03 00:00:00 2004-04-03 13:11:10 48 2004-04-03 00:00:00 2004-04-03 13:11:10 49 2004-04-03 00:00:00 2004-04-03 13:11:10 50 2004-04-03 00:00:00 2004-04-03 13:11:10 51 drop table t_dates; 52 SELECT DATE_FORMAT('123456', '%Y-%m-%d %H:%i:%s'); 53 invalid input: invalid datetime value 123456 54 SELECT DATE_FORMAT('2023-11-08 12:00', '%Y-%m-%d %H:%i:%s'); 55 DATE_FORMAT(2023-11-08 12:00, %Y-%m-%d %H:%i:%s) 56 2023-11-08 12:00:00 57 SELECT DATE_FORMAT('2023-11-08 15:00:00', '%Y-%m-%d %H:%i:%s'); 58 DATE_FORMAT(2023-11-08 15:00:00, %Y-%m-%d %H:%i:%s) 59 2023-11-08 15:00:00 60 SELECT DATE_FORMAT('2023-11-08 15:00:', '%Y-%m-%d %H:%i:%s'); 61 DATE_FORMAT(2023-11-08 15:00:, %Y-%m-%d %H:%i:%s) 62 2023-11-08 15:00:00 63 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'); 64 invalid input: invalid datetime value 19980021000000 65 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'); 66 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) 67 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|Saturday 68 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'); 69 invalid input: invalid datetime value 19981321000000 70 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'); 71 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) 72 00|12|0|12|00|AM|12:00:00 AM|00|00:00:00| October|Wednesday|21st|1998|98|Wed|Oct|294|10|21|12|00|Wednesday 73 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'); 74 invalid input: invalid datetime value 19981033000000 75 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'); 76 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) 77 00|12|0|12|00|AM|12:00:00 AM|00|00:00:00| October|Saturday|31st|1998|98|Sat|Oct|304|10|31|12|00|Saturday