Help, Is there a DATEADD function in DB2?

rockdave35 · #1 (**permalink**) 01-21-09, 16:43

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!

n_i · #2 (**permalink**) 01-21-09, 17:16

Code:

select birthdate + 52 years from employee where nickname = 'rockdave35'

Marcus_A · #3 (**permalink**) 01-21-09, 22:43

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.

bklr · #4 (**permalink**) 01-22-09, 00:08

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

nick.ncs · #5 (**permalink**) 01-22-09, 00:29

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

dr_te_z · #6 (**permalink**) 01-22-09, 01:51

DB2 Basics: Fun with Dates and Times

rockdave35 · #7 (**permalink**) 01-23-09, 09:50

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?