0

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?

Improve this question
8
  • 3
    MySQL's date format is YYYY-MM-DD HH:MM:SS. Commented Jun 15, 2016 at 15:07
  • Why isn't it stored like this in the DB? Commented Jun 15, 2016 at 15:08
  • If it's a DATETIME column, it is. What database client are you using? Commented Jun 15, 2016 at 15:08
  • 1
    And that's really all there is to say about that. Commented Jun 15, 2016 at 15:08
  • 1
    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 Commented Jun 15, 2016 at 15:10

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Okay, so to perform a query on the date time I have to use the 'YYYY-MM-DD' format?
@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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.