Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-04-06
7
Medium Priority
?
325 Views
Last Modified: 2008-02-01
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
Comment
Question by:jon100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18864693
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18864706
Perhaps this is what you need:
SELECT CASE
                    WHEN @YourDateTimeValue > GETDATE() THEN DATEADD(month, @X, @YourDateTimeValue)
                    ELSE DATEADD(month, @X, GETDATE())
              END
0
 
LVL 9

Expert Comment

by:dan_neal
ID: 18864725
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:jon100
ID: 18865009
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 18865401
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
 

Author Comment

by:jon100
ID: 18867592
gerat thanks.

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

rather than a select.
0
 

Author Comment

by:jon100
ID: 18867737
great thanks for your help.

Think i got it sorted!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question