Some programmer way back when stored dates our database in MMDDYY format in a numeric field. I'm trying to write a UDF to convert this into a date type field. This is the code that I'm using ...
create function drdata.mmddyy(mydate decimal(6,0))
return CAST(left('20' || right(mydate,2) || '-' || right('0' || trim(mydate),6),2) || '-' || substr(right('0' || trim(mydate),6),3,2) as DATE);
The function generates just fine. However, when using it the function in an SQL statement, I recieve the following error ...
SQL State: 22007
Vendor Code: -180
Message: [SQL0180] Syntax of date, time, or timestamp value not valid. Cause . . . . . : The string representation of a date, time, or timestamp value does not conform to the syntax for the specified or implied data type and format. SQL_RETURN.SQLF_OUTPUT is either the character string constant that is not valid or the column or host variable that contained the string. If the name is *N, then the value is an expression specified in the statement. If the string was found in a host variable, the host variable number is 1. Recovery . . . : Ensure that the date, time, or timestamp value conforms to the syntax for the data type it represents. Try the request again.
So, the question is, then, how do I figure out what the proper string representation that needs to be crafted?
I've queried for the system date and it comes in YYYY-MM-DD format. That is what the example function currently puts out and results in the error. I've tried every other format I can think of to no avail.
Can anyone tell me how to determine what format it's looking for? Or, maybe there is something wrong with my cast statement that is causing this?