Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date functions in DB2

Posted on 2004-08-25
7
Medium Priority
?
55,443 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:itssreekant
[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
7 Comments
 
LVL 15

Expert Comment

by:jdlambert1
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

by:
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

by:dbms_chu
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

by:BigSchmuh
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 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