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

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
bobdylan75Asked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
you can try

select date(substr(your_column, 1, 4) ||'.'||substr(your_column,5,2)||'.'||substr(your_column,7,2)) from your_table
0
 
bobdylan75Author 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?
0
 
momi_sabagCommented:
don't know about that
but you can probably create a user defined function that will do that for you
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Gary PattersonVP Technology / Senior Consultant 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
0
 
bobdylan75Author Commented:
mOMI, MY V5R4M0 SAYS THAT OPERATOR || DOESNT EXIST..
why?
0
 
momi_sabagCommented:
don't know
try to use the concatenate function instead

0
 
bobdylan75Author Commented:
thanks
0
 
Gary PattersonVP Technology / Senior Consultant 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.