3 days before date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

3 days before date

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-10 : 22:33:37

How can I get the date for 3 days before today?

Example today's date 11 May.
I would like to get 8,9,10 May.

select ... from ...
where
CONVERT(date,goDate)=DATEADD(Day,-1, datediff(day, 0,getdate()) )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 22:35:37

[code]
SELECT *
FROM table
WHERE goDate>=DATEADD(Day,datediff(day, 0,getdate()),-3)
AND goDate< DATEADD(Day,datediff(day, 0,getdate()),0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-10 : 22:39:51

Oh got it thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 22:49:07

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-14 : 07:32:06

You can find more such examples here
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-15 : 02:47:12

how can i get data for every 1st of every month, of the previous month?

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-15 : 05:07:43

I get this but it return for this month.
How to get for previous month?

select dateadd(month,datediff(month,0,getdate()),-0) as first_day_of_month
select dateadd(month,datediff(month,0,getdate())+1,-1) as last_day_of_month

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-15 : 05:17:19

[code]Select DateAdd(Month,DateDiff(Month,0,getdate())-1,0) as firstday,
DateAdd(dd,-1,DateAdd(Month,DateDiff(Month,0,getdate()),0)) as lastday
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 22:04:18

quote:
Originally posted by peace

I get this but it return for this month.
How to get for previous month?

select dateadd(month,datediff(month,0,getdate()),-0) as first_day_of_month
select dateadd(month,datediff(month,0,getdate())+1,-1) as last_day_of_month

the part in blue is one which calculates the month elapsed since start of base date (1900). the current way it returns month count till current month because of getdate. you can subtract 1,2,3 etc depending how many months before you want to traverse from current month

see this also to see similar date logics

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-16 : 04:21:43

quote:
Originally posted by peace

I get this but it return for this month.
How to get for previous month?

select dateadd(month,datediff(month,0,getdate()),-0) as first_day_of_month
select dateadd(month,datediff(month,0,getdate())+1,-1) as last_day_of_month

Have you seen the link I posted? Loot at the examples shown as last page

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources