Solved

DB2 convert to date format

Posted on 2010-11-10
3
1,470 Views
Last Modified: 2012-05-10
DB2 string like the following MDDYYYY or 0 meaning null.  How to convert to date?  Thanks.

0
1312007
12312007
0
Comment
Question by:ewang1205
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 450 total points
ID: 34103014
Hi ewang,

Oddly, you can get DB2 to produce a date-like value similar to what you show from a date field, but going from that value to a date is a bit more involved.  You simply need to build the data string yourself.

The code below will be close.


Good Luck,
Kent

SELECT 
  case when myvalue = 0 then NULL
       else date (right (myvalue, 4) || '-' || 
            cast (mod (cast (myvalue as integer) / 1000, 100) as char(2)) || '-' ||
            right ('0' || cast ((cast (myvalue as integer) / 100000) as varchar (2)), 2))
  end mydate
FROM ....

Open in new window

0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 450 total points
ID: 34103097
An easier to read SQL might look like the code below.


Kent

SELECT date (case LENGTH (myvalue)
       when 8 then right (myvalue, 4) || '-' || substr (myvalue, 3, 2) || '-' || left (myvalue, 2)
       when 7 then right (myvalue, 4) || '-' || substr (myvalue, 2, 2) || '-0' || left (myvalue, 1)
       else NULL
       end) mydate
FROM ....

Open in new window

0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 50 total points
ID: 34106222
You might also do something like RIGHT('00000000' concat TRIM(myvalue), 8)... assuming MYVALUE is a CHAR(8).

Tom
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

734 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