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

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!...
0
jon100
Asked:
jon100
  • 3
  • 3
1 Solution
 
Anthony PerkinsCommented:
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)
0
 
Anthony PerkinsCommented:
Perhaps this is what you need:
SELECT CASE
                    WHEN @YourDateTimeValue > GETDATE() THEN DATEADD(month, @X, @YourDateTimeValue)
                    ELSE DATEADD(month, @X, GETDATE())
              END
0
 
dan_nealCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jon100Author Commented:
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
0
 
Anthony PerkinsCommented:
Than something like this:

Declare @NumberOfMonths integer

Set @NumberOfMonths = 12
SELECT CASE
                    WHEN YourDateTimeColumn > GETDATE() THEN DATEADD(month, @NumberOfMonths, YourDateTimeColumn)
                    ELSE DATEADD(month, @NumberOfMonths, GETDATE())
              END
From YourTableName
0
 
jon100Author Commented:
gerat thanks.

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

rather than a select.
0
 
jon100Author Commented:
great thanks for your help.

Think i got it sorted!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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