CODEWITHSUNDEEP
mysqldatetime
Shahzaib Ahmed
Shahzaib Ahmed

Reputation: 171

My Sql TIME(STR_TO_DATE) function is not working

I am trying to store time value in my database, but STR_TO DATE FUNCTION is not working in mysql but when i try it on w3school sql engine it is working fine with the same format.

INSERT INTO `discussions`( `Name`, `Topic`, `Author`, `Auth_ID`, `day`, `Date`, `Time`, `Activated`, `Topic_ID`) 
VALUES ('asdfgh','PCB Designing','ShahzaibAhmed',1,'Sunday','2018-10-28',Time(STR_TO_DATE( '03:57:AM', '%h:%i:%p' ) ),1,2);

This is the error i am getting when i try it

enter image description here

Also when i m changing it to date only it is working fine and inserting correctly without any errors

INSERT INTO `discussions`( `Name`, `Topic`, `Author`, `Auth_ID`, `day`, `Date`, `Time`, `Activated`, `Topic_ID`) 
VALUES ('asdfgh','PCB Designing','ShahzaibAhmed',1,'Sunday','2018-10-28',Time(STR_TO_DATE( '20120801', '%Y%m%d' ) ),1,2)

enter image description here

Upvotes: 1

Views: 520

Answers (1)

danblack
danblack

Reputation: 14761

Seems to be dependent on the MySQL version:

SELECT STR_TO_DATE( '03:57:AM', '%h:%i:%p' )

Parses the time in MySQL-5.6, however not in MySQL-5.7 or MySQL-8.0.

MariaDB-10.3 and 10.2 correctly parses a result.

MySQL bug 71386 highlights that the sql_mode=NO_ZERO_DATE, being the default in later MySQL versions, is having an effect, even though its only a time.

MariaDB-10.3 returns the correct result event when sql_mode=NO_ZERO_DATE is set.

So to work around your bug, disable the 'NO_ZERO_DATE' in the session before this query:

set session sql_mode=REPLACE(@@SESSION.sql_mode,'NO_ZERO_DATE','')

Upvotes: 2

Related Questions