Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Convert MySQL date string to a NULL value exception handling     - Recycle Bin function realization in Linux (Linux)

- Java String type time compare the size (Programming)

- List Leaves (Programming)

- HAProxy performance under high concurrency (Server)

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

- Zabbix installation and configuration process (Server)

- How to force Linux users to change the initial password the first time you log in (Linux)

- Shell scripts get a snapshot of the page and generates thumbnails (Linux)

- Text analysis tools - awk (Linux)

- Linux system security knowledge (Linux)

- Ubuntu the ARP (arptables) (Linux)

- Docker + OpenvSwitch build experimental environment VxLAN (Server)

- Shell Scripting Interview Questions (Programming)

- Java enum use (Programming)

- Hadoop + Zookeeper NameNode achieve high availability (Server)

- Linux, Google Test (GTest) test environment to build step (Linux)

- Ubuntu treated with cue file to ape and wav files automatically track points (Linux)

- HTTPS Encryption Algorithm (Linux)

- To use iostat display I / O status under Linux (Linux)

- Btrfs file system creation and their characteristics in Linux (Linux)

 
         
  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:      
 
- OpenGL shadow map (Programming)
- Redis data types Introduction (Database)
- JavaScript subarray Deduplication (Programming)
- Notebook computer forget password solution (Linux)
- Port is not being used, how will bind failure? (Server)
- CentOS7 iptables (Linux)
- Use Linux built-in firewall to improve network access control (Linux)
- The need to avoid a gap of InnoDB lock (Database)
- Tab set to four spaces in Vim (Linux)
- Windows 7 hard disk to install Ubuntu 14.10 (Linux)
- Linux - Common process the command (Linux)
- Two minutes thoroughly so that you understand Android Activity Lifecycle (Programming)
- How to install and configure the KVM and Open vSwitch on Linux distributions (Linux)
- SSH mutual trust configuration (Server)
- Linux centos rm directory does not prompt (Linux)
- How to install new fonts on Ubuntu 14.04 and 14.10 (Linux)
- Security Configuration SQL Server 2000 database tutorial (Linux)
- Oracle set and remove columns unavailable (Database)
- Ubuntu Thunderbird 24.4.0 (Linux)
- How do you know your public IP terminal in Linux (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.