CODEWITHSUNDEEP
mysql
membersound
membersound

Reputation: 86677

How to format string to time in mysql?

SELECT STR_TO_DATE('07:15:00','%H:%i:%s') as time
SELECT STR_TO_DATE('07:15:00','%T') as time

Both results in NULL. But why? I don't think the format is incorrect here, but why is my time nor parsed?

+------+
| time |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.01 sec)

Moreover, I only care for the hours and minutes. But this also fails:

SELECT STR_TO_DATE('07:15:00','%H:%i') as time

My final goal is to create a view, selecting strings from another table and converting them to TIME columns:

CREATE VIEW
myview AS SELECT
SELECT STR_TO_DATE('07:15:00','%H:%i') as time
FROM `othertable`

Upvotes: 0

Views: 163

Answers (4)

forpas
forpas

Reputation: 164069

Cast the time strings to time:

SELECT cast('07:15:00' as time) as time

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

I suppose you're using version 5.7.

Look at SELECT @@GLOBAL.sql_mode if NO_ZERO_DATE value returns within the string. [ Btw, NO_ZERO_IN_DATE and NO_ZERO_DATE parameters are deprecated ]

If you're using Database at NO_ZERO_DATE, then SELECT STR_TO_DATE('07:15:00','%H:%i:%s') or SELECT STR_TO_DATE('07:15:00','%T') returns null.

Try to disable that mode by SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION'; if you have privilege, or you can edit mysql.cnf file and restart mysql.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

If you cannot disable NO_ZERO_DATE, then here is a workaround you may use which still goes through STR_TO_DATE to generate time values. You may concatenate to the time a random date, and then call TIME() to extract only the time portion, e.g.

SELECT TIME(STR_TO_DATE(CONCAT('2019-01-01 ', '07:15:00'), '%Y-%m-%d %H:%i:%s'))
--                             ^^ random date  ^^ your time literal

Upvotes: 0

Joe F
Joe F

Reputation: 13

SELECT DATE_FORMAT('2019-11-05','%Y%m%d') as time

try this function

Upvotes: -1

Related Questions