user2899728 user2899728 - 6 months ago 8
SQL Question

Why does the CAST() function return the wrong date?

I am trying to get the date part from a timestamp field.
I used this SQL query:

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


I got the following result:

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


As shown above, the date field produced returns the wrong date
2016-05-16
whereas the original date is
2016-05-15
.

How can we resolve this issue?

Answer

Use date not cast because is not casting but a format

select timestamp, date(timestamp) as my_date from messages
Comments