asked on

easy SQL: if date field before today - make it X months from now. else make it X months from its value

SQL 2000.

I have a datetime value - if it is after today - I need to add X months (int value passed into sproc) to it - otherwise I need to add X months to today's date.

can I do this in one sql statement? what is the most efficient option?

thanks!...

Anthony Perkins

Not following you, but take a look at the DATEADD() function. To add X months you would do something like this:

SELECT DATEADD(month, @X, @YourDateTyimeValue)

Anthony Perkins

Perhaps this is what you need:
SELECT CASE
WHEN @YourDateTimeValue > GETDATE() THEN DATEADD(month, @X, @YourDateTimeValue)
ELSE DATEADD(month, @X, GETDATE())
END

dan_neal

Have you looked at using CASE statements?
Such as:
CASE WHEN datefield > getdate() THEN dateadd(d,X,datefield) ELSE dateadd(d,x,getdate())

You can also use this to subtract days from the field or current date by inserting a negative number for X

jon100

ASKER

hi
sorry if its not clear. Ive got the DATEADD thing down. If the date in a field is before todays date - set the date in that field to today +12 months. If its after todays date - set it to its value + 12 months (number of months may vary).
Let me give you an example

(today's date is 06-apr-2007)
table;
12-jan-2007 becomes 06-Apr-2007
12-Jul-2007 becomes 12-Jul-2008
24-Feb-2006 becomes 06-Apr-2007

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jon100

ASKER

gerat thanks.

just one thing - i need to do an update - where an id = @id

rather than a select.

jon100

ASKER

great thanks for your help.

Think i got it sorted!