CODEWITHSUNDEEP
sqlverticato-date
CJR
CJR

Reputation: 21

Incorrect date returning when casting from varchar to date

When casting from a varchar data type to a date datatype, my query results are altering the day of the original field. For example, the below two queries

     select to_Date('2017-12-15 00:11:10.167664+00', 'YYYY-MM-DD')

     select '2017-12-15 00:11:10.167664+00'::date

return a value of "2017-12-14". I am querying a vertica database using DataGrip.

Upvotes: 2

Views: 315

Answers (1)

Jerry Chi
Jerry Chi

Reputation: 147

You can just take the leftmost 10 characters of the string and then convert to date, e.g.:

SELECT TO_DATE(LEFT('2017-12-15 00:11:10.167664+00',10), 'YYYY-MM-DD')

Upvotes: 0

Related Questions