Solved

Date functions in DB2

Posted on 2004-08-25
7
55,420 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 68 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 66 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 66 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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 (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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

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