github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_todate.result (about) 1 SELECT to_date('01,5,2013','%d,%m,%Y'); 2 to_date('01,5,2013','%d,%m,%Y') 3 2013-05-01 4 SELECT to_date('May 1, 2013','%M %d,%Y'); 5 to_date('May 1, 2013','%M %d,%Y') 6 2013-05-01 7 SELECT to_date('a09:30:17','a%h:%i:%s'); 8 to_date('a09:30:17','a%h:%i:%s') 9 09:30:17 10 SELECT to_date('a09:30:17','%h:%i:%s'); 11 to_date('a09:30:17','%h:%i:%s') 12 null 13 SELECT to_date('09:30:17a','%h:%i:%s'); 14 to_date('09:30:17a','%h:%i:%s') 15 09:30:17 16 SELECT to_date('abc','abc'); 17 to_date('abc','abc') 18 null 19 SELECT to_date('9','%m'); 20 to_date('9','%m') 21 null 22 SELECT to_date('9','%s'); 23 to_date('9','%s') 24 00:00:09 25 SELECT to_date('00/00/0000', '%m/%d/%Y'); 26 to_date('00/00/0000', '%m/%d/%Y') 27 null 28 SELECT to_date('04/31/2004', '%m/%d/%Y'); 29 to_date('04/31/2004', '%m/%d/%Y') 30 2004-04-31 31 SELECT to_date('00/00/0000', '%m/%d/%Y'); 32 to_date('00/00/0000', '%m/%d/%Y') 33 null 34 SELECT to_date('200442 Monday', '%X%V %W'); 35 to_date('200442 Monday', '%X%V %W') 36 2004-10-18 37 SELECT CAST(to_date('nope','%d-%m-%Y') AS YEAR); 38 CAST(to_date('nope','%d-%m-%Y') AS YEAR) 39 null 40 select to_date(concat_ws(' ','15-01-2001'), ' 2:59:58.999'); 41 to_date(concat_ws(' ','15-01-2001'), ' 2:59:58.999') 42 null 43 create table t1 (date char(30), format char(30) not null); 44 insert into t1 values 45 ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'), 46 ('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'), 47 ('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'), 48 ('03-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'), 49 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'), 50 ('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'), 51 ('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'), 52 ('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'), 53 ('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'), 54 ('10:20:10', '%H:%i:%s'), 55 ('10:20:10', '%h:%i:%s.%f'), 56 ('10:20:10', '%T'), 57 ('10:20:10AM', '%h:%i:%s%p'), 58 ('10:20:10AM', '%r'), 59 ('10:20:10.44AM', '%h:%i:%s.%f%p'), 60 ('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'), 61 ('15 September 2001', '%d %M %Y'), 62 ('15 SEPTEMB 2001', '%d %M %Y'), 63 ('15 MAY 2001', '%d %b %Y'), 64 ('15th May 2001', '%D %b %Y'), 65 ('Sunday 15 MAY 2001', '%W %d %b %Y'), 66 ('Sund 15 MAY 2001', '%W %d %b %Y'), 67 ('Tuesday 00 2002', '%W %U %Y'), 68 ('Thursday 53 1998', '%W %u %Y'), 69 ('Sunday 01 2001', '%W %v %x'), 70 ('Tuesday 52 2001', '%W %V %X'), 71 ('060 2004', '%j %Y'), 72 ('4 53 1998', '%w %u %Y'), 73 ('15-01-2001', '%d-%m-%Y %H:%i:%S'), 74 ('15-01-20', '%d-%m-%y'), 75 ('15-2001-1', '%d-%Y-%c'); 76 select date,format,to_date(date, format) as to_date from t1; 77 date format to_date 78 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12 79 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02 80 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02 81 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02 82 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12 83 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450000 84 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450000 85 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450000 86 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 87 10:20:10 %H:%i:%s null 88 10:20:10 %h:%i:%s.%f null 89 10:20:10 %T null 90 10:20:10AM %h:%i:%s%p null 91 10:20:10AM %r null 92 10:20:10.44AM %h:%i:%s.%f%p null 93 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 94 15 September 2001 %d %M %Y 2001-09-15 00:00:00 95 15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00 96 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 97 15th May 2001 %D %b %Y 2001-05-15 00:00:00 98 Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 99 Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 100 Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00 101 Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00 102 Sunday 01 2001 %W %v %x 2001-01-07 00:00:00 103 Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00 104 060 2004 %j %Y 2004-02-29 00:00:00 105 4 53 1998 %w %u %Y 1998-12-31 00:00:00 106 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 107 15-01-20 %d-%m-%y 2020-01-15 00:00:00 108 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 109 select date,format,concat_ws(',',to_date(date, format)) as con from t1; 110 date format con 111 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12.000000 112 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02.000000 113 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02.000000 114 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02.000000 115 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12.000000 116 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450 117 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450 118 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450 119 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12.000000 120 10:20:10 %H:%i:%s 121 10:20:10 %h:%i:%s.%f 122 10:20:10 %T 123 10:20:10AM %h:%i:%s%p 124 10:20:10AM %r 125 10:20:10.44AM %h:%i:%s.%f%p 126 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58.000000 127 15 September 2001 %d %M %Y 2001-09-15 00:00:00.000000 128 15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00.000000 129 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00.000000 130 15th May 2001 %D %b %Y 2001-05-15 00:00:00.000000 131 Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00.000000 132 Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00.000000 133 Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00.000000 134 Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00.000000 135 Sunday 01 2001 %W %v %x 2001-01-07 00:00:00.000000 136 Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00.000000 137 060 2004 %j %Y 2004-02-29 00:00:00.000000 138 4 53 1998 %w %u %Y 1998-12-31 00:00:00.000000 139 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00.000000 140 15-01-20 %d-%m-%y 2020-01-15 00:00:00.000000 141 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00.000000 142 select date,format,cast(to_date(date, format) as datetime) as datetime from t1; 143 date format datetime 144 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12 145 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02 146 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02 147 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02 148 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12 149 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12 150 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12 151 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12 152 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 153 10:20:10 %H:%i:%s null 154 10:20:10 %h:%i:%s.%f null 155 10:20:10 %T null 156 10:20:10AM %h:%i:%s%p null 157 10:20:10AM %r null 158 10:20:10.44AM %h:%i:%s.%f%p null 159 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 160 15 September 2001 %d %M %Y 2001-09-15 00:00:00 161 15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00 162 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 163 15th May 2001 %D %b %Y 2001-05-15 00:00:00 164 Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 165 Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 166 Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00 167 Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00 168 Sunday 01 2001 %W %v %x 2001-01-07 00:00:00 169 Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00 170 060 2004 %j %Y 2004-02-29 00:00:00 171 4 53 1998 %w %u %Y 1998-12-31 00:00:00 172 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 173 15-01-20 %d-%m-%y 2020-01-15 00:00:00 174 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 175 select date,format,DATE(to_date(date, format)) as date2 from t1; 176 date format date2 177 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 178 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 179 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 180 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 181 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 182 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 183 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 184 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 185 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 186 10:20:10 %H:%i:%s null 187 10:20:10 %h:%i:%s.%f null 188 10:20:10 %T null 189 10:20:10AM %h:%i:%s%p null 190 10:20:10AM %r null 191 10:20:10.44AM %h:%i:%s.%f%p null 192 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 193 15 September 2001 %d %M %Y 2001-09-15 194 15 SEPTEMB 2001 %d %M %Y 2001-09-15 195 15 MAY 2001 %d %b %Y 2001-05-15 196 15th May 2001 %D %b %Y 2001-05-15 197 Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 198 Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 199 Tuesday 00 2002 %W %U %Y 2002-01-01 200 Thursday 53 1998 %W %u %Y 1998-12-31 201 Sunday 01 2001 %W %v %x 2001-01-07 202 Tuesday 52 2001 %W %V %X 2002-01-01 203 060 2004 %j %Y 2004-02-29 204 4 53 1998 %w %u %Y 1998-12-31 205 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 206 15-01-20 %d-%m-%y 2020-01-15 207 15-2001-1 %d-%Y-%c 2001-01-15 208 select concat_ws('',to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')); 209 concat_ws('',to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')) 210 2003-01-02 08:11:02.123456 211 delete from t1; 212 insert into t1 values 213 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'), 214 ('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'), 215 ('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'), 216 ('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'), 217 ('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'), 218 ('10:20:10AM', '%H:%i:%s%p'), 219 ('15 Septembei 2001', '%d %M %Y'), 220 ('15 Ju 2001', '%d %M %Y'), 221 ('Sund 15 MA', '%W %d %b %Y'), 222 ('Thursdai 12 1998', '%W %u %Y'), 223 ('Sunday 01 2001', '%W %v %X'), 224 ('Tuesday 52 2001', '%W %V %x'), 225 ('Tuesday 52 2001', '%W %V %Y'), 226 ('Tuesday 52 2001', '%W %u %x'), 227 ('7 53 1998', '%w %u %Y'); 228 select date,format,to_date(date, format) as to_date from t1; 229 date format to_date 230 2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p null 231 2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p null 232 2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p null 233 2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p null 234 2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p null 235 10:20:10AM %H:%i:%s%p null 236 15 Septembei 2001 %d %M %Y null 237 15 Ju 2001 %d %M %Y null 238 Sund 15 MA %W %d %b %Y null 239 Thursdai 12 1998 %W %u %Y null 240 Sunday 01 2001 %W %v %X null 241 Tuesday 52 2001 %W %V %x null 242 Tuesday 52 2001 %W %V %Y null 243 Tuesday 52 2001 %W %u %x null 244 7 53 1998 %w %u %Y null 245 select date,format,concat_ws(" ",to_date(date, format),'') as con from t1; 246 date format con 247 2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p 248 2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p 249 2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p 250 2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p 251 2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p 252 10:20:10AM %H:%i:%s%p 253 15 Septembei 2001 %d %M %Y 254 15 Ju 2001 %d %M %Y 255 Sund 15 MA %W %d %b %Y 256 Thursdai 12 1998 %W %u %Y 257 Sunday 01 2001 %W %v %X 258 Tuesday 52 2001 %W %V %x 259 Tuesday 52 2001 %W %V %Y 260 Tuesday 52 2001 %W %u %x 261 7 53 1998 %w %u %Y 262 drop table t1; 263 select to_date( 1, NULL ); 264 to_date( 1, NULL ) 265 null 266 select to_date( NULL, 1 ); 267 to_date( NULL, 1 ) 268 null 269 select to_date( 1, IF(1=1,NULL,NULL) ); 270 to_date( 1, IF(1=1,NULL,NULL) ) 271 null 272 select to_date('04 /30/2004', '%m /%d/%Y'); 273 to_date('04 /30/2004', '%m /%d/%Y') 274 2004-04-30 275 select to_date('04/30 /2004', '%m /%d /%Y'); 276 to_date('04/30 /2004', '%m /%d /%Y') 277 2004-04-30 278 select to_date('04/30/2004 ', '%m/%d/%Y '); 279 to_date('04/30/2004 ', '%m/%d/%Y ') 280 2004-04-30 281 SELECT DATE_SUB(to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 282 DATE_SUB(to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE) 283 9999-12-31 00:00:00 284 SELECT DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 285 DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE) 286 9999-12-31 00:00:00 287 select month(to_date(null, '%m')), month(to_date(null, '%m')); 288 month(to_date(null, '%m')) month(to_date(null, '%m')) 289 null null 290 select to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; 291 to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE 292 22:10:00 293 select to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; 294 to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 295 1 296 select to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; 297 to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' 298 1 299 select to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; 300 to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' 301 1 302 select to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; 303 to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6' 304 1 305 select to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; 306 to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6' 307 1 308 select to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; 309 to_date('1000-01-01','%Y-%m-%d') between NULL and NULL 310 null 311 select to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; 312 to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' 313 null 314 select to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 315 and '2007/10/20'; 316 to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 317 and '2007/10/20' 318 null 319 select to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 320 and '2007/10/20 00:00:00 GMT'; 321 to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 322 and '2007/10/20 00:00:00 GMT' 323 1 324 CREATE TABLE t1 ( a datetime(2) ); 325 CREATE TABLE t2 ( a timestamp(2) ); 326 SELECT to_date('09:22', '%H:%i'); 327 to_date('09:22', '%H:%i') 328 09:22:00 329 SELECT to_date('09:22:23.33', '%H:%i:%s.%f'); 330 to_date('09:22:23.33', '%H:%i:%s.%f') 331 09:22:23.330000000 332 INSERT INTO t1 select( to_date('09:22', '%H:%i') ); 333 INSERT INTO t1 select( to_date('09:22:23.33', '%H:%i:%s.%f') ); 334 select * from t1; 335 a 336 2022-06-10 09:22:00 337 2022-06-10 09:22:23.330000000 338 DELETE FROM t1; 339 INSERT INTO t1 select( to_date('2019-12-31', '%Y-%m-%d') ); 340 INSERT INTO t2 select( to_date('09:22', '%H:%i') ); 341 INSERT INTO t2 select( to_date('09:22:23.33', '%H:%i:%s.%f') ); 342 select * from t1; 343 a 344 2019-12-31 00:00:00 345 select * from t2; 346 a 347 2022-06-10 09:22:00 348 2022-06-10 09:22:23.330000000 349 DELETE FROM t2; 350 drop table t1; 351 drop table t2;