date calculations

Question

date calculations

simflex-897410

Hall of Fame

Points: 3881
More actions
November 9, 2010 at 10:17 am

#226235

Greetings (again).
I hope this is quick and painless.
How can I obtain current date + 1 day -3 calendar months?
that is, current date plus 1 day minus 3 calendar months?
many thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

select convert(date,dateadd(mm,-3,GETDATE()+1))

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

simflex-897410

Hall of Fame

Points: 3881

November 9, 2010 at 10:44 am

#1248446

sorry but that is error'ing out.

Thanks for your help.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

Are you using SQL 2008 ?If not then it will error out.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Is this what you are seeking:

SELECT DATEADD(mm,-3,DATEADD(day, 1, GETDATE()))As '3 months ago',GETDATE() AS 'Today',

DATEADD(day, 1, GETDATE()) AS 'Today plus 1 day'

Results:

3 months ago Today Today plus 1 day

2010-08-10 12:59:22.5602010-11-09 12:59:22.5602010-11-10 12:59:22.560

Additional columns added only to check for accuracy.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

bitbucket-25253 (11/9/2010)
Is this what you are seeking:
SELECT DATEADD(mm,-3,DATEADD(day, 1, GETDATE()))As '3 months ago',GETDATE() AS 'Today',
DATEADD(day, 1, GETDATE()) AS 'Today plus 1 day'
Results:
3 months ago Today Today plus 1 day
2010-08-10 12:59:22.5602010-11-09 12:59:22.5602010-11-10 12:59:22.560
Additional columns added only to check for accuracy.

What if the OP only needs the date not the time part as suggested ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

WayneS SSC Guru Points: 95461 More actions · Answer 6

Sachin.Nandanwar (11/9/2010)
bitbucket-25253 (11/9/2010)
Is this what you are seeking:
SELECT DATEADD(mm,-3,DATEADD(day, 1, GETDATE()))As '3 months ago',GETDATE() AS 'Today',
DATEADD(day, 1, GETDATE()) AS 'Today plus 1 day'
Results:
3 months ago Today Today plus 1 day
2010-08-10 12:59:22.5602010-11-09 12:59:22.5602010-11-10 12:59:22.560
Additional columns added only to check for accuracy.
What if the OP only needs the date not the time part as suggested ?

SELECT DATEADD(mm,-3,DATEADD(day, DateDiff(day, 0, GetDate())+1, GETDATE()))As '3 months ago'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 7

Yes, I am grateful to all of you for your assistance.

Reason I asked for that code is that I have dateA and dateB

So I am trying a conditional if or case:

if dateB is less than dateA, then

do something

otherwise

do nothing

So, I assign

dateA= SELECT DATEADD(mm,-3,DATEADD(day, DateDiff(day, 0, GetDate()), GETDATE()))As '3 months ago'

Then I assign

dateB=SELECT DATEADD(mm,-3,DATEADD(day, DateDiff(day, 0, GetDate())+1, GETDATE()))As '3 months ago'

finally, I write the conditional:

If dateB < dateA then

'I will create a log on historyTable

else

'nothing to log'

The issue is ensuring that I can calculate the difference btw dateB and dateA

Again, many thanks. Highly appreciated.