Solved

DB2 convert to date format

Posted on 2010-11-10
3
1,415 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
  • 2
3 Comments
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 450 total points
Comment Utility
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:
Kdo earned 450 total points
Comment Utility
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
Comment Utility
You might also do something like RIGHT('00000000' concat TRIM(myvalue), 8)... assuming MYVALUE is a CHAR(8).

Tom
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now