Define a field using a query
Data analysis
I'm trying to create a date field from a numeric field and can't seem to get the syntax correct and can't find any help on line. Question
Data analysis
I'm trying to create a date field from a numeric field and can't seem to get the syntax correct and can't find any help on line. 
Subscribe to Alerts! Get questions and answers delivered to your Inbox.
Answer Wiki (Improve, edit or add to this answer)
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
Let's say that we have a numeric field, where July 26, 2007 would be 20070726.
DB2 for Linux/Unix/Windows: DATE(TRIM(CHAR(numericField/10000)) || TRIM(CHAR(MOD(numericField/100),100)) || TRIM(CHAR(MOD(numericField),100)))
DB2 for iSeries/zSeries: DATE(VARCHAR(numericField/10000) || VARCHAR(MOD(numericField/100),100) || VARCHAR(MOD(numericField,100))
Oracle: TO_DATE(TO_CHAR(numericField),'YYYYMMDD')
Another interesting way to do it in DB2: DATE('0001-12-31') + (numericField/10000 - 2) YEARS + MOD(numericField/100,100) MONTHS + MOD(numericField,100) DAYS
--
Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)
Browse more Questions and Answers on DataManagement.
Looking for relevant DataManagement Whitepapers? Visit the SearchDataManagement.com Research Library.
