• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 814
  • Last Modified:

DB2 need a function to return the return the current date in format yyyymmdd

hi,

I need to write a function which when called will return the current date in the format yyyymmdd

thanks
0
hraja77
Asked:
hraja77
  • 4
  • 4
1 Solution
 
Cenjoy100Commented:
SELECT CHAR(CURRENT_DATE,ISO)
      ,CHAR(CURRENT_DATE,USA)
      ,CHAR(CURRENT_DATE,EUR)
      ,CHAR(CURRENT_DATE,JIS)
FROM SYSIBM.SYSDUMMY1

---Hope Helpful for you..ISO is your required format I guess.
0
 
hraja77Author Commented:
thanks for that - how can i wrap this and create a function for the first date to be returned ?
what would the function be like and how do i call this - i could create another question for this if you want

thanks
0
 
Cenjoy100Commented:
Or this will defiantly work for now and in future
---Create and Use following UDF ....ENJOY :)

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
-----------------------------------------------------------------------
--Examples

values ts_fmt(current timestamp,'yyyymmdd')
 '20030818'
values ts_fmt(current timestamp,'asa')  
 'date format asa not recognized.'
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
hraja77Author Commented:
sorry i'm new to db2 - how do i now run the function from db2 command line
0
 
hraja77Author Commented:
i will raise another call for this part as i blieve you deserve more points
0
 
Cenjoy100Commented:
Ok,Fine once you created that I will put the comments for the same.
But its not difficult.
Trust me you will get it in flash :)
0
 
hraja77Author Commented:
thanks
0
 
Cenjoy100Commented:
Or best way you can go through the following link..
http://www.ibm.com/developerworks/data/library/techarticle/adamache/0109adamache.html

as it will be quite difficult to explain each and everything and it might stop your work for no reason.

TC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now