Using syntax from manual does not fix my date conversion issue.
Source data - 2/29/2012
Source column field type is varchar(10)
Target column field type is date
Action needed - move varchar value of 2/29/2012 to date field.
Conversion being used : cast(stg.Imp_Date AS DATE FORMAT 'MM/DD/YYYY')
I also tried cast(stg.Imp_Date AS DATE FORMAT 'MM-DD-YYYY'), as suggested by another person. Same problem.
Why do we get an error about invalidate date format?
Forcing a FORMAT on CAST for Converting Character to DATE
You can use a FORMAT phrase to convert a character string that does not match the format of
the target DATE data type. A character string in a conversion that does not specify a FORMAT
phrase uses the output format for the DATE data type.
For example, suppose the session dateform is INTEGERDATE and the default DATE format of
the system is set to 'yyyymmdd' through the tdlocaledef utility. The following statement fails,
because the character string contains separators, which does not match the default DATE
SELECT CAST ('2005-01-01' AS DATE);
To override the default DATE format, and convert a character string that contains separators,
specify a FORMAT phrase for the DATE target type:
SELECT CAST ('2005-01-01' AS DATE FORMAT 'YYYY-MM-DD');
What is the format used for? Is it used to parse the input, or format the output for the target of the update?