Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Date functions in DB2

Posted on 2004-08-25
Medium Priority
55,456 Views
i have a field in db2 table which is of type date
the field content is 28.08.1997
while selecting the field i want it to be retrived it as 28081997 ie i want to remove the seperators any ans

0
Question by:itssreekant
• 2

LVL 15

Expert Comment

ID: 11893040
SELECT Cast(Day(DateColumn) as Char(2)) + Cast(Month(DateColumn) as Char(2)) + Year(DateColumn) as Dat
FROM ...
0

LVL 15

Accepted Solution

jdlambert1 earned 272 total points
ID: 11893049
Oops. Should be:

SELECT Cast(Day(DateColumn) as Char(2)) + Cast(Month(DateColumn) as Char(2)) + Cast(Year(DateColumn) as Char(4)) as Dat
FROM ...
0

LVL 3

Assisted Solution

dbms_chu earned 264 total points
ID: 11911578
The following web link has information that will help explain what is available as far as data formatting in db2.  It also describes a way of handling this type of conversion in a UDF.
http://www-106.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html

Here is there sample function:

create function ts_fmt(TS timestamp, fmt varchar(20))
returns varchar(50)
return
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as
(
select
substr( digits (day(TS)),9),
substr( digits (month(TS)),9) ,
rtrim(char(year(TS))) ,
substr( digits (hour(TS)),9),
substr( digits (minute(TS)),9),
substr( digits (second(TS)),9),
rtrim(char(microsecond(TS)))
from sysibm.sysdummy1
)
select
case fmt
when 'yyyymmdd'
then yyyy || mm || dd
when 'mm/dd/yyyy'
then mm || '/' || dd || '/' || yyyy
when 'yyyy/dd/mm hh:mi:ss'
then yyyy || '/' || mm || '/' || dd || ' ' ||
hh || ':' || mi || ':' || ss
when 'nnnnnn'
then nnnnnn
else
'date format ' || coalesce(fmt,' <null> ') ||   ' not recognized.'
end
from tmp
0

LVL 18

Assisted Solution

BigSchmuh earned 264 total points
ID: 11915411
The easiest (and fastiest) one should be:
SELECT REPLACE (CHAR(CAST (DateColumn as date), EUR),'.','')
FROM YourTable

EUR returns dd.mm.yyyy but you although have
ISO yyyy-mm-dd
USA mm/dd/yyyy
LOCAL which uses your regional settings

Hope this helps.
0

## Featured Post

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
###### Suggested Courses
Course of the Month8 days, left to enroll