Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Convert MySQL date string to a NULL value exception handling     - Linux system started to learn: how to view the Linux thread of a process (Linux)

- Several back door and log tool under Linux (Linux)

- Common Linux System Troubleshooting (Linux)

- Install the Red Hat Container Development Kit on OSX (Server)

- Linux - use chroot command (Linux)

- PostgreSQL vacuum principle of a function and parameters (Database)

- Python several standard types of built-in functions (Programming)

- Add your own kernel and ramfs based on an existing Linux LiveCD (Linux)

- Build their own recursive DNS server (Server)

- blecat: Bluetooth Gadgets (Linux)

- Linux system security check notes on performance (Linux)

- osprofiler use OpenStack Cinder Lane (Server)

- CentOS yum source configuration (Linux)

- crontab task scheduling Health Check (Linux)

- CentOS 6 rapid deployment of Java applications (Linux)

- Use pg_basebackup build PostgreSQL streaming replication environment (Database)

- What happens after the MySQL disk space is full (Database)

- Linux System Getting Started Learning: The Linux log (Linux)

- Linux kernel RCU (Read Copy Update) lock Brief - prequel (Linux)

- How to configure Apache Solr on Ubuntu 14/15 (Server)

 
         
  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:      
 
- Ubuntu install Eclipse can not find JAVA_HOME problem (Linux)
- Use Vagrant build cross-platform development environment for Python (Server)
- Security: set limits on password (Linux)
- Use pg_basebackup build PostgreSQL streaming replication environment (Database)
- MySQL stored procedures and triggers (Database)
- MySQL to recover the data through binlog (Database)
- PHP parsing algorithm of the interview questions (Programming)
- UNIX how to restrict users by IP Telnet (Linux)
- sqlplus connect to Oracle (Database)
- Java MD5 encryption implementation (Programming)
- CentOS source installation GitLab Chinese Version (Server)
- Oracle TAF Analysis (Database)
- MyCAT log analysis (Database)
- Analysis of common mistakes when compiling MySQL installation (Database)
- Use MongoDB C # MongoDB official driving operation (Database)
- MongoDB, Cassandra, HBase transaction design strategy (Database)
- Solve ORA-01012: not logged on (Database)
- CentOS7 installed MySQL (Database)
- New experience Budgie (Budgerigar) desktop environment (Linux)
- VMware virtual machine to install CentOS 6.2 (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.