user2899728 user2899728 - 2 years ago 65
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
whereas the original date is

How can we resolve this issue?

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