Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Convert MySQL date string to a NULL value exception handling     - Install Jetty on CentOS / RHEL 6.X (Server)

- MySQL remote connection settings (Database)

- linux remote control software (Linux)

- Linux file and directory permissions settings (Linux)

- Linux operating system boot manager -GRUB (Linux)

- To install and use the Doxygen under Linux (Linux)

- How to merge two pictures in Cacti (Linux)

- Android custom slideshow menu slidmenu (Programming)

- Java how to achieve bubble sort the problem Arraylist (Programming)

- Four levels of intrusion on Linux server and counter-measures (Linux)

- Build a Linux development environment under STC89C52RC (Linux)

- RedHat Performance Tuning (Server)

- Linux Network Programming - raw socket programming (Programming)

- DNF Command Tutorial (Linux)

- The formatted Linux hard drive and mount (Linux)

- Android 4.2 compilation notes (Programming)

- Introduction to thread pooling and simple implementation (Programming)

- Top 10: HTML5, JavaScript 3D game engine and framework (Linux)

- KVM add virtual disks (Linux)

- DataGuard the MRP can not start to analyze and solve problems (Database)

 
         
  Convert MySQL date string to a NULL value exception handling
     
  Add Date : 2018-11-21      
         
         
         
  1, YYYYmmdd date format is converted to null

See the following records can take to a value less than the value of taking a **
mysql> SELECT DATE_FORMAT (STR_TO_DATE ( '20150922 13:01:01', '% Y% m% d% H:% m:% s'), '% H:% m');
+ ------------------------- +
| DATE_FORMAT (STR_TO_DATE ( '20150922 13:01:01', '% Y% m% d% H:% m:% s'), '% H:% m') |
+ ------------------------- +
| 13:01 |
+ ------------------------- +
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT (STR_TO_DATE ( '20150922 12:55:00', '% Y% m% d% H:% m:% s'), '% H:% m');
+ ------------------------- +
| DATE_FORMAT (STR_TO_DATE ( '20150922 12:55:00', '% Y% m% d% H:% m:% s'), '% H:% m') |
+ ------------------------- +
| NULL |
+ ------------------------- +
1 row in set, 1 warning (0.00 sec)

mysql>

2, see the error message:

mysql> show warnings;
+ --- + - + ------------------------ +
| Level | Code | Message |
+ --- + - + ------------------------ +
| Warning | 1411 | Incorrect datetime value: '20150922 12:55:00' for function str_to_date |
+ --- + - + ------------------------ +
1 row in set (0.00 sec)

mysql>
Police say is Incorrect datetime value: '20150922 12:55:00' for function str_to_date, incorrect date format, it is replaced with a fairly standard date format

3, replaced with the canonical% Y-% m-% d% H:% i:% s try

mysql> select DATE_FORMAT (str_to_date ( '2015-09-22 13:00:01', '% Y-% m-% d% H:% i:% s'), '% H:% i');
+ -------------------------- +
| DATE_FORMAT (str_to_date ( '2015-09-22 13:00:01', '% Y-% m-% d% H:% i:% s'), '% H:% i') |
+ -------------------------- +
| 13:00 |
+ -------------------------- +
1 row in set (0.00 sec)

mysql> select DATE_FORMAT (str_to_date ( '2015-09-22 12:55:00', '% Y-% m-% d% H:% i:% s'), '% H:% i');
+ -------------------------- +
| DATE_FORMAT (str_to_date ( '2015-09-22 12:55:00', '% Y-% m-% d% H:% i:% s'), '% H:% i') |
+ -------------------------- +
| 12:55 |
+ -------------------------- +
1 row in set (0.00 sec)

mysql>

See in the canonical format, date conversion from string to date format, then the interception division are able to get the value.

Refer to the official website address: http: //dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date

4, it is determined to narrow again,% h is the problem, when you get into uppercase

To see a case, the format will become 2015-09-22 13:00:01 table ready format, you can still take less time value, as follows:

mysql> select DATE_FORMAT (str_to_date ( '2015-09-22 13:00:01', '% Y-% m-% d% h:% i:% s'), '% h:% i');
+ ------------------------------------------------- ----------------------------- +
| DATE_FORMAT (str_to_date ( '2015-09-22 13:00:01', '% Y-% m-% d% h:% i:% s'), '% h:% i') |
+ ------------------------------------------------- ----------------------------- +
| NULL |
+ ------------------------------------------------- ----------------------------- +
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_FORMAT (str_to_date ( '2015-09-22 12:55:00', '% Y-% m-% d% h:% i:% s'), '% h:% i');
+ ------------------------------------------------- ----------------------------- +
| DATE_FORMAT (str_to_date ( '2015-09-22 12:55:00', '% Y-% m-% d% h:% i:% s'), '% h:% i') |
+ ------------------------------------------------- ----------------------------- +
| 12:55 |
+ ------------------------------------------------- ----------------------------- +
1 row in set (0.00 sec)

mysql>
Then the problem is where? Only Quguan network again searching for answers to find http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_get-format
See the ultimate standard format: '.% Y-% m-% d% H.% i% s', into the try, to see examples of where the difference yet? The difference is that% H and% h ah, as follows:

mysql> select DATE_FORMAT (str_to_date ( '20150922 13:00:01', '% Y% m% d% h:% i:% s'), '% h:% i');
+ ------------------------------------------------- ------------------------- +
| DATE_FORMAT (str_to_date ( '20150922 13:00:01', '% Y% m% d% h:% i:% s'), '% h:% i') |
+ ------------------------------------------------- ------------------------- +
| NULL |
+ ------------------------------------------------- ------------------------- +
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_FORMAT (str_to_date ( '20150922 12:55:00', '% Y% m% d% h:% i:% s'), '% h:% i');
+ ------------------------------------------------- ------------------------- +
| DATE_FORMAT (str_to_date ( '20150922 12:55:00', '% Y% m% d% h:% i:% s'), '% h:% i') |
+ ------------------------------------------------- ------------------------- +
| 12:55 |
+ ------------------------------------------------- ------------------------- +
1 row in set (0.00 sec)

mysql>
Then the standard date format:% Y% m% d% H:% i:% s 'or% Y-% m-% d% H:% i:% s' are as follows:

select DATE_FORMAT (str_to_date ( '20150922 13:00:01', '% Y% m% d% H:% i:% s'), '% H:% i');
select DATE_FORMAT (str_to_date ( '20150922 12:55:00', '% Y% m% d% H:% i:% s'), '% H:% i');
     
         
         
         
  More:      
 
- CentOS 7 Docker build private warehouse registry (Linux)
- gzip, bzip2, xz, tar, zip compression, archive Detailed (Linux)
- Linux Command Tutorial: Ubuntu apt-get command (Linux)
- OpenCV cvFindCornerSubPix () to find sub-pixel Corner (Programming)
- Java precision four operations (Programming)
- How to implement large-scale distributed Yahoo depth study on the Hadoop cluster (Server)
- CentOS replaces update source and Linux kernel compilation summary (Linux)
- PostgreSQL Stream Configuration (Database)
- Memory leak analysis using Android studio (Programming)
- mysqldump implement database logical backup (Database)
- Talk about the Linux ABI compatibility Application (Linux)
- Java threads in the life cycle (Programming)
- RedHat Linux source code package installation process (Linux)
- How to install OpenOffice Ubuntu or Linux Mint (Linux)
- Python MySQL database connection (Database)
- C ++ based foundation: the difference between C and C ++ (Programming)
- Each catalog Detailed Linux (Linux)
- httpd-2.4 feature (Server)
- RedHat Linux 6.5 Enterprise Edition installation Redis 3.0.3 (Database)
- After VMware CentOS full VM clone the network card is unavailable Solutions (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.