Date functions in DB2

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

thanks in advance
itssreekantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jdlambert1Connect With a Mentor Commented:
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
 
jdlambert1Commented:
SELECT Cast(Day(DateColumn) as Char(2)) + Cast(Month(DateColumn) as Char(2)) + Year(DateColumn) as Dat
FROM ...
0
 
dbms_chuConnect With a Mentor Commented:
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
 
BigSchmuhConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.