Why doesn't my DateTime search work?

Question

Sorry about the generic title, I didn't know how to phrase this.

I have a DateTime in my MySQL DB. For example: 14/06/2016 15:01:00

When I try to do a query to find dates which equal certain dates it won't find anything, unless I used the Americanised date format. Yet it's stored in the English way.

Eg:

Select * FROM tbl WHERE Date = '14/06/2016' - Doesn't return any results

But Select * FROM tbl WHERE Date = '2016/06/14' does return results.

Why is this? And how can I swap it around?

If it's a `DATETIME` column, it is. What database client are you using? — ceejayoz, Jun 15 '16 at 15:08
Possible duplicate of [Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS' when creating a table](http://stackoverflow.com/questions/8338031/mysql-setup-the-format-of-datetime-to-dd-mm-yyyy-hhmmss-when-creating-a-tab) — ceejayoz, Jun 15 '16 at 15:09
I guess your Client-Software is "converting" the columns displayed format for "ease of use". - However as mentioned, the real data format is `YYYY-MM-DD HH:MM:SS` — dognose, Jun 15 '16 at 15:10
@AndrewKilburn https://www.devart.com/dbforge/mysql/studio/history.html says "Date format can now be set for the Data Editor", so I suspect your client is displaying dates in a different format than in the DB. — ceejayoz, Jun 15 '16 at 15:10

score 2 · Answer 1 · answered Jun 15 '16 at 15:13

2

http://dev.mysql.com/doc/refman/5.7/en/datetime.html

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

If you're seeing a different format, your SQL client is "helpfully" changing the output from the underlying data storage.

answered Jun 15 '16 at 15:13

ceejayoz

176,543
40
303
368

Okay, so to perform a query on the date time I have to use the 'YYYY-MM-DD' format? – Andrew Kilburn Jun 15 '16 at 15:14
@AndrewKilburn Yes, you should. It looks like it's correctly interpreting your `YYYY/MM/DD` but you shouldn't count on that - for best results, use the format MySQL expects. – ceejayoz Jun 15 '16 at 15:16

Why doesn't my DateTime search work?

1 Answers1