user2899728 user2899728 - 2 years ago 82
SQL Question

MySQL: Why CAST() function gives wrong date?

I am trying to get date part from timestamp field.
I used this sql query:

select timestamp, CAST(timestamp as date) as date from messages

I got following result:

| timestamp | date |
| 2016-05-15 10:22:54 | 2016-05-16 |

As shown above date field produced wrong date 16 whereas original date is 15.

Please guide that why is that happening?

How can we resolve this issue?

I'll appreciate any contribution.


Answer Source

Use date not cast because is not casting but a format

select timestamp, date(timestamp) as my_date from messages
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download