github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_tests/tidb_mysql_test/r/date_formats.result (about) 1 set tidb_cost_model_version=1; 2 select str_to_date(concat('15-01-2001',' 2:59:58.999'), 3 concat('%d-%m-%Y',' ','%H:%i:%s.%f')); 4 str_to_date(concat('15-01-2001',' 2:59:58.999'), 5 concat('%d-%m-%Y',' ','%H:%i:%s.%f')) 6 2001-01-15 02:59:58.999000 7 select STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T'); 8 STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T') 9 NULL 10 SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 11 create table t1 (date char(30), format char(30) not null); 12 insert into t1 values 13 ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'), 14 ('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'), 15 ('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'), 16 ('03-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'), 17 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'), 18 ('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'), 19 ('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'), 20 ('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'), 21 ('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'), 22 ('10:20:10', '%H:%i:%s'), 23 ('10:20:10', '%h:%i:%s.%f'), 24 ('10:20:10', '%T'), 25 ('10:20:10AM', '%h:%i:%s%p'), 26 ('10:20:10AM', '%r'), 27 ('10:20:10.44AM', '%h:%i:%s.%f%p'), 28 ('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'), 29 ('15 September 2001', '%d %M %Y'), 30 ('15 SEPTEMB 2001', '%d %M %Y'), 31 ('15 MAY 2001', '%d %b %Y'), 32 ('15th May 2001', '%D %b %Y'), 33 ('Sunday 15 MAY 2001', '%W %d %b %Y'), 34 ('Sund 15 MAY 2001', '%W %d %b %Y'), 35 ('Tuesday 00 2002', '%W %U %Y'), 36 ('Thursday 53 1998', '%W %u %Y'), 37 ('Sunday 01 2001', '%W %v %x'), 38 ('Tuesday 52 2001', '%W %V %X'), 39 ('060 2004', '%j %Y'), 40 ('4 53 1998', '%w %u %Y'), 41 ('15-01-2001', '%d-%m-%Y %H:%i:%S'), 42 ('15-01-20', '%d-%m-%y'), 43 ('15-2001-1', '%d-%Y-%c'); 44 select date,format,str_to_date(date, format) as str_to_date from t1; 45 date format str_to_date 46 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12.000000 47 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02.000000 48 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02.000000 49 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02.000000 50 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12.000000 51 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450 52 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450 53 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450 54 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12.000000 55 10:20:10 %H:%i:%s 0000-00-00 10:20:10.000000 56 10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10.000000 57 10:20:10 %T 0000-00-00 10:20:10.000000 58 10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10.000000 59 10:20:10AM %r 0000-00-00 10:20:10.000000 60 10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000 61 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58.000000 62 15 September 2001 %d %M %Y 2001-09-15 00:00:00.000000 63 15 SEPTEMB 2001 %d %M %Y NULL 64 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00.000000 65 15th May 2001 %D %b %Y NULL 66 Sunday 15 MAY 2001 %W %d %b %Y NULL 67 Sund 15 MAY 2001 %W %d %b %Y NULL 68 Tuesday 00 2002 %W %U %Y NULL 69 Thursday 53 1998 %W %u %Y NULL 70 Sunday 01 2001 %W %v %x NULL 71 Tuesday 52 2001 %W %V %X NULL 72 060 2004 %j %Y 2004-00-00 00:00:00.000000 73 4 53 1998 %w %u %Y NULL 74 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00.000000 75 15-01-20 %d-%m-%y 2020-01-15 00:00:00.000000 76 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00.000000 77 select date,format,concat('',str_to_date(date, format)) as con from t1; 78 date format con 79 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12.000000 80 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02.000000 81 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02.000000 82 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02.000000 83 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12.000000 84 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450 85 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450 86 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450 87 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12.000000 88 10:20:10 %H:%i:%s 0000-00-00 10:20:10.000000 89 10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10.000000 90 10:20:10 %T 0000-00-00 10:20:10.000000 91 10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10.000000 92 10:20:10AM %r 0000-00-00 10:20:10.000000 93 10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000 94 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58.000000 95 15 September 2001 %d %M %Y 2001-09-15 00:00:00.000000 96 15 SEPTEMB 2001 %d %M %Y NULL 97 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00.000000 98 15th May 2001 %D %b %Y NULL 99 Sunday 15 MAY 2001 %W %d %b %Y NULL 100 Sund 15 MAY 2001 %W %d %b %Y NULL 101 Tuesday 00 2002 %W %U %Y NULL 102 Thursday 53 1998 %W %u %Y NULL 103 Sunday 01 2001 %W %v %x NULL 104 Tuesday 52 2001 %W %V %X NULL 105 060 2004 %j %Y 2004-00-00 00:00:00.000000 106 4 53 1998 %w %u %Y NULL 107 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00.000000 108 15-01-20 %d-%m-%y 2020-01-15 00:00:00.000000 109 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00.000000 110 select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1; 111 date format datetime 112 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12 113 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02 114 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02 115 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02 116 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12 117 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12 118 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12 119 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12 120 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 121 10:20:10 %H:%i:%s 0000-00-00 10:20:10 122 10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10 123 10:20:10 %T 0000-00-00 10:20:10 124 10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10 125 10:20:10AM %r 0000-00-00 10:20:10 126 10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10 127 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 128 15 September 2001 %d %M %Y 2001-09-15 00:00:00 129 15 SEPTEMB 2001 %d %M %Y NULL 130 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 131 15th May 2001 %D %b %Y NULL 132 Sunday 15 MAY 2001 %W %d %b %Y NULL 133 Sund 15 MAY 2001 %W %d %b %Y NULL 134 Tuesday 00 2002 %W %U %Y NULL 135 Thursday 53 1998 %W %u %Y NULL 136 Sunday 01 2001 %W %v %x NULL 137 Tuesday 52 2001 %W %V %X NULL 138 060 2004 %j %Y 2004-00-00 00:00:00 139 4 53 1998 %w %u %Y NULL 140 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 141 15-01-20 %d-%m-%y 2020-01-15 00:00:00 142 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 143 select date,format,DATE(str_to_date(date, format)) as date2 from t1; 144 date format date2 145 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 146 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 147 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 148 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 149 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 150 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 151 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 152 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 153 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 154 10:20:10 %H:%i:%s 0000-00-00 155 10:20:10 %h:%i:%s.%f 0000-00-00 156 10:20:10 %T 0000-00-00 157 10:20:10AM %h:%i:%s%p 0000-00-00 158 10:20:10AM %r 0000-00-00 159 10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 160 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 161 15 September 2001 %d %M %Y 2001-09-15 162 15 SEPTEMB 2001 %d %M %Y NULL 163 15 MAY 2001 %d %b %Y 2001-05-15 164 15th May 2001 %D %b %Y NULL 165 Sunday 15 MAY 2001 %W %d %b %Y NULL 166 Sund 15 MAY 2001 %W %d %b %Y NULL 167 Tuesday 00 2002 %W %U %Y NULL 168 Thursday 53 1998 %W %u %Y NULL 169 Sunday 01 2001 %W %v %x NULL 170 Tuesday 52 2001 %W %V %X NULL 171 060 2004 %j %Y 2004-00-00 172 4 53 1998 %w %u %Y NULL 173 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 174 15-01-20 %d-%m-%y 2020-01-15 175 15-2001-1 %d-%Y-%c 2001-01-15 176 select date,format,TIME(str_to_date(date, format)) as time from t1; 177 date format time 178 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12.000000 179 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02.000000 180 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02.000000 181 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02.000000 182 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12.000000 183 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.123450 184 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450 185 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450 186 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12.000000 187 10:20:10 %H:%i:%s 10:20:10.000000 188 10:20:10 %h:%i:%s.%f 10:20:10.000000 189 10:20:10 %T 10:20:10.000000 190 10:20:10AM %h:%i:%s%p 10:20:10.000000 191 10:20:10AM %r 10:20:10.000000 192 10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000 193 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58.000000 194 15 September 2001 %d %M %Y 00:00:00.000000 195 15 SEPTEMB 2001 %d %M %Y NULL 196 15 MAY 2001 %d %b %Y 00:00:00.000000 197 15th May 2001 %D %b %Y NULL 198 Sunday 15 MAY 2001 %W %d %b %Y NULL 199 Sund 15 MAY 2001 %W %d %b %Y NULL 200 Tuesday 00 2002 %W %U %Y NULL 201 Thursday 53 1998 %W %u %Y NULL 202 Sunday 01 2001 %W %v %x NULL 203 Tuesday 52 2001 %W %V %X NULL 204 060 2004 %j %Y 00:00:00.000000 205 4 53 1998 %w %u %Y NULL 206 15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00.000000 207 15-01-20 %d-%m-%y 00:00:00.000000 208 15-2001-1 %d-%Y-%c 00:00:00.000000 209 select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1; 210 date format time2 211 2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12.000000 212 03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02.000000 213 0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02.000000 214 03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02.000000 215 2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12.000000 216 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.123450 217 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450 218 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450 219 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12.000000 220 10:20:10 %H:%i:%s 10:20:10.000000 221 10:20:10 %h:%i:%s.%f 10:20:10.000000 222 10:20:10 %T 10:20:10.000000 223 10:20:10AM %h:%i:%s%p 10:20:10.000000 224 10:20:10AM %r 10:20:10.000000 225 10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000 226 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58.000000 227 15 September 2001 %d %M %Y 00:00:00.000000 228 15 SEPTEMB 2001 %d %M %Y NULL 229 15 MAY 2001 %d %b %Y 00:00:00.000000 230 15th May 2001 %D %b %Y NULL 231 Sunday 15 MAY 2001 %W %d %b %Y NULL 232 Sund 15 MAY 2001 %W %d %b %Y NULL 233 Tuesday 00 2002 %W %U %Y NULL 234 Thursday 53 1998 %W %u %Y NULL 235 Sunday 01 2001 %W %v %x NULL 236 Tuesday 52 2001 %W %V %X NULL 237 060 2004 %j %Y 00:00:00.000000 238 4 53 1998 %w %u %Y NULL 239 15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00.000000 240 15-01-20 %d-%m-%y 00:00:00.000000 241 15-2001-1 %d-%Y-%c 00:00:00.000000 242 select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')); 243 concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')) 244 2003-01-02 08:11:02.123456 245 truncate table t1; 246 insert into t1 values 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 (NULL, get_format(DATE,'USA')); 263 select date,format,str_to_date(date, format) as str_to_date from t1; 264 date format str_to_date 265 2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL 266 2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p NULL 267 2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p NULL 268 2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p NULL 269 2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p NULL 270 10:20:10AM %H:%i:%s%p NULL 271 15 Septembei 2001 %d %M %Y NULL 272 15 Ju 2001 %d %M %Y NULL 273 Sund 15 MA %W %d %b %Y NULL 274 Thursdai 12 1998 %W %u %Y NULL 275 Sunday 01 2001 %W %v %X NULL 276 Tuesday 52 2001 %W %V %x NULL 277 Tuesday 52 2001 %W %V %Y NULL 278 Tuesday 52 2001 %W %u %x NULL 279 7 53 1998 %w %u %Y NULL 280 NULL %m.%d.%Y NULL 281 select date,format,concat(str_to_date(date, format),'') as con from t1; 282 date format con 283 2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL 284 2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p NULL 285 2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p NULL 286 2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p NULL 287 2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p NULL 288 10:20:10AM %H:%i:%s%p NULL 289 15 Septembei 2001 %d %M %Y NULL 290 15 Ju 2001 %d %M %Y NULL 291 Sund 15 MA %W %d %b %Y NULL 292 Thursdai 12 1998 %W %u %Y NULL 293 Sunday 01 2001 %W %v %X NULL 294 Tuesday 52 2001 %W %V %x NULL 295 Tuesday 52 2001 %W %V %Y NULL 296 Tuesday 52 2001 %W %u %x NULL 297 7 53 1998 %w %u %Y NULL 298 NULL %m.%d.%Y NULL 299 truncate table t1; 300 insert into t1 values 301 ('10:20:10AM', '%h:%i:%s'), 302 ('2003-01-02 10:11:12', '%Y-%m-%d %h:%i:%S'), 303 ('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'); 304 select date,format,str_to_date(date, format) as str_to_date from t1; 305 date format str_to_date 306 10:20:10AM %h:%i:%s 0000-00-00 10:20:10.000000 307 2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12.000000 308 03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12.000000 309 select date,format,concat(str_to_date(date, format),'') as con from t1; 310 date format con 311 10:20:10AM %h:%i:%s 0000-00-00 10:20:10.000000 312 2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12.000000 313 03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12.000000 314 SET sql_mode = default; 315 select get_format(DATE, 'USA') as a; 316 a 317 %m.%d.%Y 318 select get_format(TIME, 'internal') as a; 319 a 320 321 select get_format(DATETIME, 'eur') as a; 322 a 323 324 select get_format(TIMESTAMP, 'eur') as a; 325 a 326 327 select get_format(DATE, 'TEST') as a; 328 a 329 330 select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA')); 331 str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA')) 332 NULL 333 explain select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"),cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001"); 334 id estRows task access object operator info 335 Projection_3 1.00 root 1997-01-01->Column#1, <nil>->Column#2, <nil>->Column#3, 00:00:00->Column#4, 12:59:59->Column#5, 23:11:12->Column#6, 1->Column#7 336 └─TableDual_4 1.00 root rows:1 337 create table t2 (d date); 338 insert into t2 values ('2004-07-14'),('2005-07-14'); 339 select date_format(d,"%d") from t2 order by 1; 340 date_format(d,"%d") 341 14 342 14 343 select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a; 344 a 345 2003-01-02 10:11:12.001200 346 SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 347 create table t3(f1 datetime ,f2 datetime,f3 datetime,f4 datetime,f5 datetime); 348 insert into t3 values (str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"),str_to_date("10:11:12.0012", "%H:%i:%S.%f"), 349 str_to_date("2003-01-02", "%Y-%m-%d"), str_to_date("02", "%d") , str_to_date("02 10", "%d %H")); 350 describe t3; 351 Field Type Null Key Default Extra 352 f1 datetime YES NULL 353 f2 datetime YES NULL 354 f3 datetime YES NULL 355 f4 datetime YES NULL 356 f5 datetime YES NULL 357 select * from t3; 358 f1 f2 f3 f4 f5 359 2003-01-02 10:11:12 # 2003-01-02 00:00:00 0000-00-02 00:00:00 0000-00-02 10:00:00 360 create table t4(a text , b text); 361 Insert into t4 values ("02 10", "%d %H"); 362 select str_to_date(a,b) from t4; 363 str_to_date(a,b) 364 0000-00-02 10:00:00.000000 365 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1, 366 str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2, 367 str_to_date("2003-01-02", "%Y-%m-%d") as f3, 368 str_to_date("02 10:11:12", "%d %H:%i:%S.%f") as f4, 369 str_to_date("02 10:11:12", "%d %H:%i:%S") as f5, 370 str_to_date("02 10", "%d %f") as f6; 371 f1 f2 f3 f4 f5 f6 372 2003-01-02 10:11:12.001200 2003-01-02 10:11:12 2003-01-02 0000-00-02 10:11:12.000000 0000-00-02 10:11:12 0000-00-02 00:00:00.100000 373 select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1, 374 addtime("-01:01:01.01 GGG", "-23:59:59.1") as f2, 375 microsecond("1997-12-31 23:59:59.01XXXX") as f3; 376 f1 f2 f3 377 2003-01-02 10:11:12.001200 NULL 10000 378 select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1, 379 str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2; 380 f1 f2 381 2003-04-05 2003-04-05 10:11:12.101010 382 SET sql_mode = default; 383 set names latin1; 384 select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); 385 date_format('2004-01-01','%W (%a), %e %M (%b) %Y') 386 Thursday (Thu), 1 January (Jan) 2004 387 set names latin1; 388 select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); 389 date_format('2004-01-01','%W (%a), %e %M (%b) %Y') 390 Thursday (Thu), 1 January (Jan) 2004 391 set names latin1; 392 create table t5 (f1 datetime); 393 insert into t5 (f1) values ("2005-01-01"); 394 insert into t5 (f1) values ("2005-02-01"); 395 select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t5 order by date_format(f1, "%M"); 396 d1 d2 397 02 February 398 01 January 399 select str_to_date( 1, NULL ); 400 str_to_date( 1, NULL ) 401 NULL 402 select str_to_date( NULL, 1 ); 403 str_to_date( NULL, 1 ) 404 NULL 405 select str_to_date( 1, IF(1=1,NULL,NULL) ); 406 str_to_date( 1, IF(1=1,NULL,NULL) ) 407 NULL 408 SELECT TIME_FORMAT("24:00:00", '%r'); 409 TIME_FORMAT("24:00:00", '%r') 410 12:00:00 AM 411 SELECT TIME_FORMAT("00:00:00", '%r'); 412 TIME_FORMAT("00:00:00", '%r') 413 12:00:00 AM 414 SELECT TIME_FORMAT("12:00:00", '%r'); 415 TIME_FORMAT("12:00:00", '%r') 416 12:00:00 PM 417 SELECT TIME_FORMAT("15:00:00", '%r'); 418 TIME_FORMAT("15:00:00", '%r') 419 03:00:00 PM 420 SELECT TIME_FORMAT("01:00:00", '%r'); 421 TIME_FORMAT("01:00:00", '%r') 422 01:00:00 AM 423 SELECT TIME_FORMAT("25:00:00", '%r'); 424 TIME_FORMAT("25:00:00", '%r') 425 01:00:00 AM 426 SELECT TIME_FORMAT("00:00:00", '%l %p'); 427 TIME_FORMAT("00:00:00", '%l %p') 428 12 AM 429 SELECT TIME_FORMAT("01:00:00", '%l %p'); 430 TIME_FORMAT("01:00:00", '%l %p') 431 1 AM 432 SELECT TIME_FORMAT("12:00:00", '%l %p'); 433 TIME_FORMAT("12:00:00", '%l %p') 434 12 PM 435 SELECT TIME_FORMAT("23:00:00", '%l %p'); 436 TIME_FORMAT("23:00:00", '%l %p') 437 11 PM 438 SELECT TIME_FORMAT("24:00:00", '%l %p'); 439 TIME_FORMAT("24:00:00", '%l %p') 440 12 AM 441 SELECT TIME_FORMAT("25:00:00", '%l %p'); 442 TIME_FORMAT("25:00:00", '%l %p') 443 1 AM 444 SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896); 445 DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896) 446 NULL 447 select str_to_date('04 /30/2004', '%m /%d/%Y'); 448 str_to_date('04 /30/2004', '%m /%d/%Y') 449 2004-04-30 450 select str_to_date('04/30 /2004', '%m /%d /%Y'); 451 str_to_date('04/30 /2004', '%m /%d /%Y') 452 2004-04-30 453 select str_to_date('04/30/2004 ', '%m/%d/%Y '); 454 str_to_date('04/30/2004 ', '%m/%d/%Y ') 455 2004-04-30 456 "End of 4.1 tests" 457 SELECT DATE_FORMAT("0000-01-01",'%W %d %M %Y') as valid_date; 458 valid_date 459 Saturday 01 January 0000 460 SELECT DATE_FORMAT("0000-02-28",'%W %d %M %Y') as valid_date; 461 valid_date 462 Monday 28 February 0000 463 SELECT DATE_FORMAT("2009-01-01",'%W %d %M %Y') as valid_date; 464 valid_date 465 Thursday 01 January 2009 466 "End of 5.0 tests" 467 # 468 # Start of 5.1 tests 469 # 470 # 471 # Bug#58005 utf8 + get_format causes failed assertion: !str || str != Ptr' 472 # 473 SET NAMES utf8; 474 SELECT LEAST('%', GET_FORMAT(datetime, 'eur'), CAST(GET_FORMAT(datetime, 'eur') AS CHAR(65535))); 475 LEAST('%', GET_FORMAT(datetime, 'eur'), CAST(GET_FORMAT(datetime, 'eur') AS CHAR(65535))) 476 477 SET NAMES latin1; 478 # 479 # End of 5.1 tests 480 # 481 # 482 # Start of 5.6 tests 483 # 484 # 485 # WL#946 Fractional seconds precision 486 # Testing Item_func_date_format with NULL argument. 487 # 488 SELECT CAST(TIME_FORMAT(NULL, '%s') AS CHAR); 489 CAST(TIME_FORMAT(NULL, '%s') AS CHAR) 490 NULL 491 SELECT CAST(TIME_FORMAT(NULL, '%s') AS SIGNED); 492 CAST(TIME_FORMAT(NULL, '%s') AS SIGNED) 493 NULL 494 SELECT CAST(TIME_FORMAT(NULL, '%s') AS DECIMAL(23,6)); 495 CAST(TIME_FORMAT(NULL, '%s') AS DECIMAL(23,6)) 496 NULL 497 SELECT CAST(TIME_FORMAT(NULL, '%s') AS TIME); 498 CAST(TIME_FORMAT(NULL, '%s') AS TIME) 499 NULL 500 SELECT CAST(TIME_FORMAT(NULL, '%s') AS DATE); 501 CAST(TIME_FORMAT(NULL, '%s') AS DATE) 502 NULL 503 SELECT CAST(TIME_FORMAT(NULL, '%s') AS DATETIME); 504 CAST(TIME_FORMAT(NULL, '%s') AS DATETIME) 505 NULL 506 SELECT TIME_FORMAT(NULL, '%s')+0e0; 507 TIME_FORMAT(NULL, '%s')+0e0 508 NULL 509 # 510 # End of 5.6 tests 511 # 512 # 513 # Bug#19047644 EXTRACT_DATE_TIME MISBEHAVES WITH 514 # UNINITIALISED VALUE ON GARBAGE INPUTS 515 # 516 do str_to_date(1, "%#"); 517 # 518 # Bug#19047488 MAKE_DATE_TIME WITH TOO BIG STRING ARGUMENT, 519 # INVALID MEMORY READS 520 # 521 do timestamp(date_format('2011-11-11', right("12345" + 1, 3))); 522 # 523 # Bug #25949639: DATE FORMAT 'YYYYMMDD' ISN'T RECOGNIZED IN LEFT JOIN 524 # 525 CREATE TABLE t6 (a varchar(10), PRIMARY KEY (a)); 526 CREATE TABLE t7 (a varchar(10), b date, PRIMARY KEY(a,b)); 527 CREATE TABLE t8 (a varchar(10), b TIME, PRIMARY KEY(a,b)); 528 INSERT INTO t6 VALUES ('test1'); 529 INSERT INTO t7 VALUES 530 ('test1','2016-12-13'),('test1','2016-12-14'),('test1','2016-12-15'); 531 INSERT INTO t8 VALUES 532 ('test1','11:13:14'), ('test1','12:13:14'), ('test1','10:13:14'); 533 ANALYZE TABLE t6, t7, t8; 534 SELECT b, b = '20161213', 535 CASE b WHEN '20161213' then 'found' ELSE 'not found' END FROM t7; 536 b b = '20161213' CASE b WHEN '20161213' then 'found' ELSE 'not found' END 537 2016-12-13 1 found 538 2016-12-14 0 not found 539 2016-12-15 0 not found 540 SELECT b, b IN ('20161213'), b in ('20161213', 0) FROM t7; 541 b b IN ('20161213') b in ('20161213', 0) 542 2016-12-13 1 1 543 2016-12-14 0 0 544 2016-12-15 0 0 545 SELECT b, b = '121314', 546 CASE b WHEN '121314' then 'found' ELSE 'not found' END FROM t8; 547 b b = '121314' CASE b WHEN '121314' then 'found' ELSE 'not found' END 548 11:13:14 0 not found 549 12:13:14 1 found 550 10:13:14 0 not found 551 SELECT b, b in ('121314'), b in ('121314', 0) FROM t8; 552 b b in ('121314') b in ('121314', 0) 553 11:13:14 0 0 554 12:13:14 1 1 555 10:13:14 0 0 556 DROP TABLE t6, t7, t8;