Solved

Date functions in DB2

Posted on 2004-08-25
7
55,398 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
  • 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DB2 what is copybook? 4 558
db2 connect on mac 3 547
Catalog Index 9 123
Best cheaper way to convert to not editable document 6 48
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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