Solved

Date functions in DB2

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
teradata sql olap functions 2 283
DB2 process keep going heavy and memory usage high 13 1,447
replication of DB2 to MS SQL 43 469
DB2 Log Shipping (Big vs Little Endianness) 4 157
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

810 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