?
Solved

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

Posted on 2011-04-26
8
Medium Priority
?
991 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
0
Comment
Question by:bobdylan75
  • 3
  • 3
  • 2
8 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 35465923
you can try

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

Author Comment

by:bobdylan75
ID: 35466014
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35466357
don't know about that
but you can probably create a user defined function that will do that for you
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 36

Expert Comment

by:Gary Patterson
ID: 35466701
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
 

Author Comment

by:bobdylan75
ID: 35466836
mOMI, MY V5R4M0 SAYS THAT OPERATOR || DOESNT EXIST..
why?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35466998
don't know
try to use the concatenate function instead

0
 

Author Closing Comment

by:bobdylan75
ID: 35467112
thanks
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 35468614
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question