github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_str_to_date.result (about) 1 drop table if exists t1; 2 create table t1( 3 cdate varchar(20), 4 ctime varchar(20), 5 cdatetime varchar(20) 6 ); 7 insert into t1 values('04/31/2004','09:30:17', '2022-05-27 11:30:00'); 8 insert into t1 values('05/31/2012','11:30:17', '2012-05-26 12:30:00'); 9 insert into t1 values('04/23/2009','01:30:17', '2002-07-26 02:30:01'); 10 insert into t1 values('01/31/2004','12:30:17', '2001-03-26 08:10:01'); 11 insert into t1 values('07/03/2018','05:30:17', '2011-08-26 07:15:01'); 12 insert into t1 values('08/25/2014','09:30:17', '2011-11-26 06:15:01'); 13 insert into t1 values('06/30/2022','04:30:17', '2011-12-26 06:15:01'); 14 SELECT cdate,STR_TO_DATE(cdate,'%m/%d/%Y') from t1; 15 cdate str_to_date(cdate, %m/%d/%Y) 16 04/31/2004 null 17 05/31/2012 2012-05-31 18 04/23/2009 2009-04-23 19 01/31/2004 2004-01-31 20 07/03/2018 2018-07-03 21 08/25/2014 2014-08-25 22 06/30/2022 2022-06-30 23 SELECT ctime,STR_TO_DATE(ctime,'%h:%i:%s') from t1; 24 ctime str_to_date(ctime, %h:%i:%s) 25 09:30:17 09:30:17 26 11:30:17 11:30:17 27 01:30:17 01:30:17 28 12:30:17 00:30:17 29 05:30:17 05:30:17 30 09:30:17 09:30:17 31 04:30:17 04:30:17 32 SELECT cdatetime,STR_TO_DATE(cdatetime,'%Y-%m-%d %H:%i:%s') from t1; 33 cdatetime str_to_date(cdatetime, %Y-%m-%d %H:%i:%s) 34 2022-05-27 11:30:00 2022-05-27 11:30:00 35 2012-05-26 12:30:00 2012-05-26 12:30:00 36 2002-07-26 02:30:01 2002-07-26 02:30:01 37 2001-03-26 08:10:01 2001-03-26 08:10:01 38 2011-08-26 07:15:01 2011-08-26 07:15:01 39 2011-11-26 06:15:01 2011-11-26 06:15:01 40 2011-12-26 06:15:01 2011-12-26 06:15:01 41 SELECT cdate,TO_DATE(cdate,'%m/%d/%Y') from t1; 42 cdate to_date(cdate, %m/%d/%Y) 43 04/31/2004 null 44 05/31/2012 2012-05-31 45 04/23/2009 2009-04-23 46 01/31/2004 2004-01-31 47 07/03/2018 2018-07-03 48 08/25/2014 2014-08-25 49 06/30/2022 2022-06-30 50 SELECT ctime,TO_DATE(ctime,'%h:%i:%s') from t1; 51 ctime to_date(ctime, %h:%i:%s) 52 09:30:17 09:30:17 53 11:30:17 11:30:17 54 01:30:17 01:30:17 55 12:30:17 00:30:17 56 05:30:17 05:30:17 57 09:30:17 09:30:17 58 04:30:17 04:30:17 59 SELECT cdatetime,TO_DATE(cdatetime,'%Y-%m-%d %H:%i:%s') from t1; 60 cdatetime to_date(cdatetime, %Y-%m-%d %H:%i:%s) 61 2022-05-27 11:30:00 2022-05-27 11:30:00 62 2012-05-26 12:30:00 2012-05-26 12:30:00 63 2002-07-26 02:30:01 2002-07-26 02:30:01 64 2001-03-26 08:10:01 2001-03-26 08:10:01 65 2011-08-26 07:15:01 2011-08-26 07:15:01 66 2011-11-26 06:15:01 2011-11-26 06:15:01 67 2011-12-26 06:15:01 2011-12-26 06:15:01 68 drop table t1; 69 drop table if exists t2; 70 create table t2( 71 cdate varchar(20), 72 ctime varchar(20), 73 cdatetime varchar(30) 74 ); 75 insert into t2 values('May 1, 2013','11:13:56','8:10:2.123456 13-01-02'); 76 insert into t2 values('Feb 28, 2022','12:33:51','12:19:2.123456 06-01-02'); 77 insert into t2 values('Jul 20, 2022','03:23:36','15:21:2.123456 22-01-02'); 78 insert into t2 values('Aug 1, 2013','01:43:46','11:11:2.123456 25-01-02'); 79 insert into t2 values('Nov 28, 2022','10:53:41','19:31:2.123456 11-01-02'); 80 insert into t2 values('Dec 20, 2022','09:23:46','1:41:2.123456 02-01-02'); 81 SELECT cdate,STR_TO_DATE(cdate,'%b %d,%Y') from t2; 82 cdate str_to_date(cdate, %b %d,%Y) 83 May 1, 2013 2013-05-01 84 Feb 28, 2022 2022-02-28 85 Jul 20, 2022 2022-07-20 86 Aug 1, 2013 2013-08-01 87 Nov 28, 2022 2022-11-28 88 Dec 20, 2022 2022-12-20 89 SELECT ctime,STR_TO_DATE(ctime,'%r') from t2; 90 ctime str_to_date(ctime, %r) 91 11:13:56 11:13:56 92 12:33:51 00:33:51 93 03:23:36 03:23:36 94 01:43:46 01:43:46 95 10:53:41 10:53:41 96 09:23:46 09:23:46 97 SELECT cdatetime,STR_TO_DATE(cdatetime,'%H:%i:%S.%f %y-%m-%d') from t2; 98 cdatetime str_to_date(cdatetime, %H:%i:%S.%f %y-%m-%d) 99 8:10:2.123456 13-01-02 2013-01-02 08:10:02 100 12:19:2.123456 06-01-02 2006-01-02 12:19:02 101 15:21:2.123456 22-01-02 2022-01-02 15:21:02 102 11:11:2.123456 25-01-02 2025-01-02 11:11:02 103 19:31:2.123456 11-01-02 2011-01-02 19:31:02 104 1:41:2.123456 02-01-02 2002-01-02 01:41:02 105 SELECT cdate,TO_DATE(cdate,'%b %d,%Y') from t2; 106 cdate to_date(cdate, %b %d,%Y) 107 May 1, 2013 2013-05-01 108 Feb 28, 2022 2022-02-28 109 Jul 20, 2022 2022-07-20 110 Aug 1, 2013 2013-08-01 111 Nov 28, 2022 2022-11-28 112 Dec 20, 2022 2022-12-20 113 SELECT ctime,TO_DATE(ctime,'%r') from t2; 114 ctime to_date(ctime, %r) 115 11:13:56 11:13:56 116 12:33:51 00:33:51 117 03:23:36 03:23:36 118 01:43:46 01:43:46 119 10:53:41 10:53:41 120 09:23:46 09:23:46 121 SELECT cdatetime,TO_DATE(cdatetime,'%H:%i:%S.%f %y-%m-%d') from t2; 122 cdatetime to_date(cdatetime, %H:%i:%S.%f %y-%m-%d) 123 8:10:2.123456 13-01-02 2013-01-02 08:10:02 124 12:19:2.123456 06-01-02 2006-01-02 12:19:02 125 15:21:2.123456 22-01-02 2022-01-02 15:21:02 126 11:11:2.123456 25-01-02 2025-01-02 11:11:02 127 19:31:2.123456 11-01-02 2011-01-02 19:31:02 128 1:41:2.123456 02-01-02 2002-01-02 01:41:02 129 drop table t2; 130 SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y'); 131 str_to_date(04/31/2004, %m/%d/%Y) 132 null 133 SELECT str_to_date('May 1, 2013','%M %d,%Y'); 134 str_to_date(May 1, 2013, %M %d,%Y) 135 2013-05-01 136 SELECT str_to_date('February 28, 2022','%M %d,%Y'); 137 str_to_date(February 28, 2022, %M %d,%Y) 138 2022-02-28 139 select str_to_date('8:10:2.123456 13-01-02','%H:%i:%S.%f %y-%m-%d'); 140 str_to_date(8:10:2.123456 13-01-02, %H:%i:%S.%f %y-%m-%d) 141 2013-01-02 08:10:02 142 select str_to_date('11:13:56', '%r'); 143 str_to_date(11:13:56, %r) 144 11:13:56 145 SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 146 date_add(str_to_date(9999-12-30 23:59:00, %Y-%m-%d %H:%i:%s), interval(1, minute)) 147 9999-12-31 00:00:00 148 SELECT str_to_date('01,5,2013','%d,%m,%Y'); 149 str_to_date(01,5,2013, %d,%m,%Y) 150 2013-05-01 151 SELECT str_to_date('May 1, 2013','%M %d,%Y'); 152 str_to_date(May 1, 2013, %M %d,%Y) 153 2013-05-01 154 SELECT str_to_date('a09:30:17','a%h:%i:%s'); 155 str_to_date(a09:30:17, a%h:%i:%s) 156 09:30:17 157 SELECT str_to_date('a09:30:17','%h:%i:%s'); 158 str_to_date(a09:30:17, %h:%i:%s) 159 null 160 SELECT str_to_date('09:30:17a','%h:%i:%s'); 161 str_to_date(09:30:17a, %h:%i:%s) 162 09:30:17 163 SELECT str_to_date('abc','abc'); 164 str_to_date(abc, abc) 165 null 166 SELECT str_to_date('9','%m'); 167 str_to_date(9, %m) 168 null 169 SELECT str_to_date('9','%s'); 170 str_to_date(9, %s) 171 00:00:09 172 SELECT str_to_date('00/00/0000', '%m/%d/%Y'); 173 str_to_date(00/00/0000, %m/%d/%Y) 174 null 175 SELECT str_to_date('04/31/2004', '%m/%d/%Y'); 176 str_to_date(04/31/2004, %m/%d/%Y) 177 null 178 SELECT str_to_date('00/00/0000', '%m/%d/%Y'); 179 str_to_date(00/00/0000, %m/%d/%Y) 180 null 181 SELECT str_to_date('200442 Monday', '%X%V %W'); 182 str_to_date(200442 Monday, %X%V %W) 183 null 184 select str_to_date(concat_ws(' ','15-01-2001'), ' 2:59:58.999'); 185 str_to_date(concat_ws( , 15-01-2001), 2:59:58.999) 186 null 187 select concat_ws('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')); 188 concat_ws(, str_to_date(8:11:2.123456 03-01-02, %H:%i:%S.%f %y-%m-%d)) 189 2003-01-02 08:11:02 190 select str_to_date('04 /30/2004', '%m /%d/%Y'); 191 str_to_date(04 /30/2004, %m /%d/%Y) 192 2004-04-30 193 select str_to_date('04/30 /2004', '%m /%d /%Y'); 194 str_to_date(04/30 /2004, %m /%d /%Y) 195 2004-04-30 196 select str_to_date('04/30/2004 ', '%m/%d/%Y '); 197 str_to_date(04/30/2004 , %m/%d/%Y ) 198 2004-04-30 199 SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 200 date_sub(str_to_date(9999-12-31 00:01:00, %Y-%m-%d %H:%i:%s), interval(1, minute)) 201 9999-12-31 00:00:00 202 SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 203 date_add(str_to_date(9999-12-30 23:59:00, %Y-%m-%d %H:%i:%s), interval(1, minute)) 204 9999-12-31 00:00:00 205 SELECT str_to_date('09:22', '%H:%i'); 206 str_to_date(09:22, %H:%i) 207 09:22:00 208 SELECT str_to_date('09:22:23.33', '%H:%i:%s.%f'); 209 str_to_date(09:22:23.33, %H:%i:%s.%f) 210 09:22:23 211 SELECT str_to_date('09:22', '%H:%i'); 212 str_to_date(09:22, %H:%i) 213 09:22:00 214 SELECT str_to_date('2008-01-01',replace(replace(replace(replace(replace(replace(replace('yyyy-MM-dd','a','%p'),'ss','%s'),'mm','%i'),'HH','%H'),'yyyy','%Y'),'dd','%d'),'MM','%m')); 215 str_to_date(2008-01-01, replace(replace(replace(replace(replace(replace(replace(yyyy-MM-dd, a, %p), ss, %s), mm, %i), HH, %H), yyyy, %Y), dd, %d), MM, %m)) 216 2008-01-01 217 SELECT TO_DATE('04/31/2004', '%m/%d/%Y'); 218 to_date(04/31/2004, %m/%d/%Y) 219 null 220 SELECT to_date('May 1, 2013','%M %d,%Y'); 221 to_date(May 1, 2013, %M %d,%Y) 222 2013-05-01 223 SELECT to_date('February 28, 2022','%M %d,%Y'); 224 to_date(February 28, 2022, %M %d,%Y) 225 2022-02-28 226 select to_date('8:10:2.123456 13-01-02','%H:%i:%S.%f %y-%m-%d'); 227 to_date(8:10:2.123456 13-01-02, %H:%i:%S.%f %y-%m-%d) 228 2013-01-02 08:10:02 229 select to_date('11:13:56', '%r'); 230 to_date(11:13:56, %r) 231 11:13:56 232 SELECT DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 233 date_add(to_date(9999-12-30 23:59:00, %Y-%m-%d %H:%i:%s), interval(1, minute)) 234 9999-12-31 00:00:00 235 SELECT to_date('01,5,2013','%d,%m,%Y'); 236 to_date(01,5,2013, %d,%m,%Y) 237 2013-05-01 238 SELECT to_date('May 1, 2013','%M %d,%Y'); 239 to_date(May 1, 2013, %M %d,%Y) 240 2013-05-01 241 SELECT to_date('a09:30:17','a%h:%i:%s'); 242 to_date(a09:30:17, a%h:%i:%s) 243 09:30:17 244 SELECT to_date('a09:30:17','%h:%i:%s'); 245 to_date(a09:30:17, %h:%i:%s) 246 null 247 SELECT to_date('09:30:17a','%h:%i:%s'); 248 to_date(09:30:17a, %h:%i:%s) 249 09:30:17 250 SELECT to_date('abc','abc'); 251 to_date(abc, abc) 252 null 253 SELECT to_date('9','%m'); 254 to_date(9, %m) 255 null 256 SELECT to_date('9','%s'); 257 to_date(9, %s) 258 00:00:09 259 SELECT to_date('00/00/0000', '%m/%d/%Y'); 260 to_date(00/00/0000, %m/%d/%Y) 261 null 262 SELECT to_date('04/31/2004', '%m/%d/%Y'); 263 to_date(04/31/2004, %m/%d/%Y) 264 null 265 SELECT to_date('00/00/0000', '%m/%d/%Y'); 266 to_date(00/00/0000, %m/%d/%Y) 267 null 268 SELECT to_date('200442 Monday', '%X%V %W'); 269 to_date(200442 Monday, %X%V %W) 270 null 271 select to_date(concat_ws(' ','15-01-2001'), ' 2:59:58.999'); 272 to_date(concat_ws( , 15-01-2001), 2:59:58.999) 273 null 274 select concat_ws('',to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')); 275 concat_ws(, to_date(8:11:2.123456 03-01-02, %H:%i:%S.%f %y-%m-%d)) 276 2003-01-02 08:11:02 277 select to_date('04 /30/2004', '%m /%d/%Y'); 278 to_date(04 /30/2004, %m /%d/%Y) 279 2004-04-30 280 select to_date('04/30 /2004', '%m /%d /%Y'); 281 to_date(04/30 /2004, %m /%d /%Y) 282 2004-04-30 283 select to_date('04/30/2004 ', '%m/%d/%Y '); 284 to_date(04/30/2004 , %m/%d/%Y ) 285 2004-04-30 286 SELECT DATE_SUB(to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 287 date_sub(to_date(9999-12-31 00:01:00, %Y-%m-%d %H:%i:%s), interval(1, minute)) 288 9999-12-31 00:00:00 289 SELECT DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 290 date_add(to_date(9999-12-30 23:59:00, %Y-%m-%d %H:%i:%s), interval(1, minute)) 291 9999-12-31 00:00:00 292 SELECT to_date('09:22', '%H:%i'); 293 to_date(09:22, %H:%i) 294 09:22:00 295 SELECT to_date('09:22:23.33', '%H:%i:%s.%f'); 296 to_date(09:22:23.33, %H:%i:%s.%f) 297 09:22:23 298 SELECT to_date('09:22', '%H:%i'); 299 to_date(09:22, %H:%i) 300 09:22:00