How to convert YYYY-MM-DD HH:MM:SS:MS to MM/DD/YYYY in snowflake-CodePudding

I'm trying to convert the date field from YYYY-MM-DD HH:MM:SS:MS to MM/DD/YYYY format in a view using Snowflake database with below condition:

TO_VARCHAR(DATE(SRC_DATE),'MM/DD/YYYY')

I'm able to convert the date to expected format with above condition, but when I try to load data from this view to different table using a sp its failing with below error:

Failed: Code: 939 - State: 22023 - Message: SQL compilation error: error line 1 at position 1,058 too many arguments for function [TO_VARCHAR(VALID_FROM_DATE, 'YYYYMMDDHH24MISS.FF9')] expected 1, got 2 - Stack Trace: Statement.execute, line 9 position 58

Need help in getting the right logic to fix this error. How can I do this?

CodePudding user response：

You just need to do:

TO_VARCHAR(SRC_DATE,'MM/DD/YYYY')

I’m wondering why you are trying to change the format of a date in Informatica? A date is held as a number, the format is just how it is displayed - the underlying number doesn’t change

CodePudding user response：

I'm not sure exactly what's going on yet, but I can walk through some things we do know.

First, the error message says the TO_VARCHAR() function only expected one argument. Knowing this we can look at the documentation for the method. Here we see there are several overloads:

TO_VARCHAR( <expr> )
TO_VARCHAR( <numeric_expr> [, '<format>' ] )
TO_VARCHAR( <date_or_time_expr> [, '<format>' ] )
TO_VARCHAR( <binary_expr> [, '<format>' ] )

Only one of these overloads (the first) fits the error message. Most of the overloads allow multiple arguments, but only if the first argument matches certain types: numeric, date/time, or binary. This does include the expected date result of the DATE() function

Therefore we can conclude somehow the result of the DATE(SRC_DATE) call is NOT a valid <date_or_time_expr> in every case, such that we at least sometimes end up with the first overload.

While the documentation for Date() does allow several ways for the function to return NULL, it also explicitly returns a Date type:

The data type of the returned value is DATE.

Thus I'd still expect it to always match the third overload above. The only other possible result from Date() is the conversion fails, in which case we'd see a different error message entirely.

The best explanation I could guess at is the return type for Date() doesn't matter if the result is NULL (that is: NULL is inherently untyped for this purpose), such that you're still ending up with the first overload, which does not allow the 2nd argument.

You could possibly fix this by adding a COALESCE() so NULL is converted to a valid consistent throw-away date expression; something like, say, 1900-01-01. If it's important to preserve NULL values you can then in turn also wrap the whole thing in a NULLIF() call.

Finally, all of this only make sense is SRC_DATE is not already a valid <date_or_time_expr>. That is, if it's something like a varchar column. This in itself would already be a mistake in schema design. On the other hand, if it is already a datetime column, there is no need at all to call Date(), and it can be used directly with TO_VARCHAR()... but with likely the same caveat about NULL values you're already seeing.

We can test this theory by trying the following:

TO_VARCHAR(DATE(IFNULL(SRC_DATE,'1900-01-01')),'MM/DD/YYYY')