We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

db2 sql: I have a numeric date yyyymmdd how to traslate in standard date field?

bobdylan75
bobdylan75 asked
on
Medium Priority
1,048 Views
Last Modified: 2012-05-11
db2 sql: I have a numeric date yyyymmdd how to traslate in standard date field?
I have the field ODATE as numeric 8 s 0
with this value for example: 20110426 -> 26/April/2011

how to traslate it in a currect date field in sql ?
thanks
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok but, considered that I have to use it several times in my sql code,
is it possible create a function in the header of the sql code?
don't know about that
but you can probably create a user defined function that will do that for you
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
Substring doesn't work on numeric fields.  Need to use DIGITS() each time forts to convert numeric to string.

Check out the iDate utility here for a robust set if date conversion functions:

http://www.think400.dk/downloads.htm

Author

Commented:
mOMI, MY V5R4M0 SAYS THAT OPERATOR || DOESNT EXIST..
why?
don't know
try to use the concatenate function instead

Author

Commented:
thanks
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
Like I said above, if your column is numeric (you said 8s0) , substring won't work by itself.  That's probably why you're getting the error on "||" operator, too (or you've got another syntax error of some sort).  

Need to add DIGITS():

select date(substr(digits(your_column), 1, 4) || '.' || substr(digits(your_column),5,2) || '.' || substr(digits(your_column),7,2)) from your_table

You can use either "CONCAT" or "||" as the concatenation operator.  Both are correct:

'A' CONCAT 'B'
-or-
'A' || 'B'

- Gary Patterson
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.