Re: Converting a numeric date value to a DATE in SQL

I have a note that I made a while back from Birgitta Hauser. It has always worked for me. The trick is to make sure the number is CAST as DEC(8,0).

--NOTE:The numeric data value must be 8,0 in order for this to work.

--If necessary, cast the source value as DEC(8,0).

--Date(Digits(A.MyNum) concat '000000')

--Example:

SELECTA.MyNum

, Digits(A.MyNum) concat '000000'

, Date(Digits(A.MyNum) concat '000000') --**HERE IS THE CONVERSION**

FROM(SELECT Cast(20100310 as Dec(8, 0)) AS MyNum FROM sysibm.sysdummy1 ) A

Notice that the literal date, 20100310, was CAST as Dec(8,0) in the example above.When using numeric values directly (without decimal positions) these values are interpreted as being Integer and the Integer format consists of 10 digits instead of 8 which are expected.


Robert
"Contrariwise, if it was so, it might be; and if it were so, it would be; but as it isn't, it ain't. That's logic."--Tweedledee