Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Convert MySQL date string to a NULL value exception handling     - How to configure AWStats in Ubuntu Server (Server)

- Java garbage collection (Programming)

- MySQL InnoDB table --BTree basic data structures (Database)

- Migu online music player for Linux (Linux)

- PHP interview questions of design patterns (Programming)

- Nodejs mysql pool Example (Programming)

- Composition and dynamic memory allocation C program (Programming)

- JavaScript function closures Quick Start (Programming)

- Linux device driver development small example --LED lights (Programming)

- Use custom backup plans for Debian backupninja (Linux)

- Oracle Data File Management (Database)

- How do you turn on and off IPv6 address on Fedora (Linux)

- Linux system components Detailed log (Linux)

- RedHat Linux 6.4 install Oracle 10g error (Database)

- Deploy OpenStack Juno on Ubuntu 14.04 (Linux)

- Ora-00439: feature not enabled: managed standby (Database)

- You know the difference between URL, URI and URN among you (Linux)

- Lua and C ++ (Programming)

- Use Hexo quickly build and deploy a blog to Github (Server)

- ntop monitoring software configuration and installation (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:      
 
- Ubuntu compiler installation R Full Record (Linux)
- SSH configuration under Linux (Linux)
- Linux kernel panic (because glibc result) Repair (Linux)
- jQuery update the content and method of use 3.0 (Programming)
- 11 you Linux Terminal Command (Linux)
- How to add a new hard disk without restarting the CentOS 7 / RHEL 7 virtual machine (Linux)
- Comparison of C # and Java (Programming)
- JavaScript common array manipulation functions and usage (Programming)
- Ubuntu uses the / etc / profile file to configure the JAVA environment variable (Linux)
- namespace mechanism Linux kernel analysis (Linux)
- CentOS 6.4 OpenNebula installation (Linux)
- Hands to teach you to solve Ubuntu error message (Linux)
- How to configure HTTP Strict Transport Security (HSTS) (Linux)
- RHEL6 install Python and other packages from source (Linux)
- Oracle SQL statement to retrieve data paging table (Database)
- You may not know the Linux command-line network monitoring tool (Linux)
- Java semaphores (Programming)
- Spacewalk remove packages install the update (Linux)
- Experience RHEL7 new features (Linux)
- ThinkPad X220 Ubuntu 14.10 installed on fingerprint recognition (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.