Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Help, Is there a DATEADD function in DB2?

    I need to run a dateadd function that will add 52 to an employee's birthdate so I can know the year that they turn 52.

    SQL has a function called dateadd, but it appears DB2 does not have this function. IS there anything equivalent to this function? If so, can someone post a syntax example? Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,431
    Code:
    select birthdate + 52 years from employee where nickname = 'rockdave35'
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    Quote Originally Posted by rockdave35
    SQL has a function called dateadd, but it appears DB2 does not have this function. IS there anything equivalent to this function? If so, can someone post a syntax example? Thanks!
    MS SQL Server has a function called dateadd. IBM (who invented SQL) uses ANSI standard SQL in DB2 which has date arithmetic as described by n_i.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    try like this
    select dateadd(yy,52,birthdate) from employee where nickname = 'rockdave35'
    Hi, Nick
    if use select getdate()+52 i will add for the no of days as 52 not for years

  5. #5
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Quote Originally Posted by bklr
    try like this
    select dateadd(yy,52,birthdate) from employee where nickname = 'rockdave35'
    I guess thats how you do in MS SQL Server.... there is no dateadd function in db2

    Quote Originally Posted by bklr
    try like this
    Hi, Nick
    if use select getdate()+52 i will add for the no of days as 52 not for years
    nick has added "52 years" and not just 52.... there is a difference... in DB2 you can directly add days ( eg. date + 10 days ) , months ( eg. date + 10 months) and years just like arithmetic addition
    IBM Certified Database Associate, DB2 9 for LUW

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

  7. #7
    Join Date
    Jan 2009
    Posts
    46
    Quote Originally Posted by nick.ncs
    I guess thats how you do in MS SQL Server.... there is no dateadd function in db2


    nick has added "52 years" and not just 52.... there is a difference... in DB2 you can directly add days ( eg. date + 10 days ) , months ( eg. date + 10 months) and years just like arithmetic addition

    The statement actually worked for me in DB2:

    Birthdate + 52 Years as AGE 52_DATE


    The only problem left is that it returns the month, day and year.
    For example

    3-16-1960 returns 3-16-2012

    I just need the year. I think there is a YEAR function that will point me in the right direction. Any ideas?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rockdave35
    I just need the year. I think there is a YEAR function that will point me in the right direction. Any ideas?
    allow me to point you in the right direction -----------------> the DB2 SQL reference manual

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    Quote Originally Posted by r937
    allow me to point you in the right direction -----------------> the DB2 SQL reference manual
    And to make it even easier:

    DB2 SQL Reference Vol 1 manual
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •