Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
to_date function returns wrong result
Hi,
I expect vertica returns error when converting to date from wrong/incomplete stringselect to_date('1230','yymmdd')
returns:2014-06-05
which doesn't make any sense. We expect incorrect date to fail to convert.
Same query in oracle correctly gives an error (not a valid month / input value not long enough for dateformat)
I found that it actually might be a feature rather than a bug,
https://www.vertica.com/blog/convert-a-string-to-a-date-including-unconventional-dates/
Is there a way how to disable this 'lenient '/unpredictable feature?
Tagged:
0
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Answers
This is actually expected because we emulate Postgres-SQL. The calculation is that '1230' are assumed to be the year 'yy' and month 'mm' portions; '30' is an illegal month, so we convert by rolling forward 24 months leaving 6 as the remainder. I've no idea why it's day 5 though.
The same result is obtained from Vertica and Postgres engines:
Vertica (10.0.0-0)
dbadmin=> select to_date('1230','yymmdd');
to_date
2014-06-05
(1 row)
psql (9.2.24)
postgres=# select to_date('1230','yymmdd');
to_date
2014-06-05
(1 row)
However, I couldn't work out a way to suppress this behavior in Vertica. The "FM" and "FX" modifiers suggested don't appear to apply here. You could open a ticket if you have access to support, but it is likely quicker to validate inputs using the date-time functions native to the client (unless using vsql or manual entry).
Maybe add in your own constraints?
Example:
thanks
i will open ticket i guess,
there should be a choice of strict enforcement, similar to type conversion with exclamation mark
Vertica was born from PostGreSQL and we inherited the TO_DATE functionality.
In versions of PostGreSQL 9.x and below, their documentation warned:
See: https://www.postgresql.org/docs/9.6/functions-formatting.html
We see this in Vertica:
But it looks like PostSreSQL fixed this issue in their verison 10 release and they removed the above warning from their documentation
See: https://www.postgresql.org/docs/10/release-10.html
In particular:
In Vertica 10 we still get this oddball result:
That being said, please open a support case. Feel free to include the above info in the case description.
When you do, can you please email the case number? I will be sure to tie your case to the Jira ticket.
My email is [email protected]
Thanks!
For the meantime can this help?