0

Just want to change this via configuration only. without change my query

Current SQL_MODE is

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I have one table dummy_test and there is one field name created_at

created_at => datetime(NULL)

When i perform below query

insert into dummy_test values('2018-08-14 05:38:11 pm')

ITs give me an error

Error in query (1292): Incorrect datetime value: '2018-08-14 05:38:11 pm' for column 'created_at' at row 1

It's working fine with 

insert into dummy_test values('2018-08-14 05:38:11')
Vishal Bareja
  • 128
  • 2
  • 11

2 Answers2

2

Per MySQL documentation:

MySQL recognizes TIME values in these formats:

  • As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', or 'SS'. Here D represents days and can have a value from 0 to 34.

  • As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as '10:11:12', but '109712' is illegal (it has a nonsensical minute part) and becomes '00:00:00'.

  • As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following alternative formats are also understood: SS, MMSS, or HHMMSS.

So you'll have to use 24-hour time when sending to MySQL. If you're passing the date/time via PHP, this question might be helpful.

Community
  • 1
  • 1
Zack
  • 2,220
  • 1
  • 8
  • 12
1

you can use STR_TO_DATE function ie.. insert into dummy_test values(STR_TO_DATE('2018-08-14 05:38:11 PM', '%Y-%m-%d %h:%i:%s %p'))

If you want, you can add a "BeforeInsert" table trigger to intercept and convert those values.

Krish
  • 5,917
  • 2
  • 14
  • 35
  • Thanks for your valuable replay but i dont want change in sql queries because my project is working on. I just want to change it by sql configuration – Vishal Bareja Aug 14 '18 at 13:12
  • Also, the format string provided doesn't match the formatted date (the format string uses slashes for the date, and `%y` matches a 2-digit year instead of a 4-digit one). – Zack Aug 14 '18 at 13:18