How to get the data .. - 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

How to get the data ..

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-12 : 21:39:24

I usually write getdate()-7 to get past 7 days worth of data,
how do i get , last 5 days worth of data delete last 2 days of days,
basically i dont want last 2 days of data...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 21:44:10

[code]
where datecol >= dateadd(day, datediff(day, 0, getdate()), -7)
and datecol < dateadd(day, datediff(day, 0, getdate()), -2)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 01:01:10

quote:
Originally posted by rds207

I usually write getdate()-7 to get past 7 days worth of data,
how do i get , last 5 days worth of data delete last 2 days of days,
basically i dont want last 2 days of data...

please keep in mind that getdate() also return time. so taking getdate()-7 will return you date 7 days before with current time. any comparison with this value will exclude all records that happened on the 7th previous day with time less than current time. so use like

DATEADD(dd,DATEDIFF(dd,0,getdate())-7,0)

instead of just
getdate()-7

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

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-18 : 01:51:32

Could you please elloborate this,
for eg i am running job at 2010-03-17 18:23:00,

i will get records between 2010-03-17 18:23:00 and 2010-03-10 18:23:00 right??

what are the records i am missing here?

quote:
Originally posted by visakh16

quote:
Originally posted by rds207

I usually write getdate()-7 to get past 7 days worth of data,
how do i get , last 5 days worth of data delete last 2 days of days,
basically i dont want last 2 days of data...

please keep in mind that getdate() also return time. so taking getdate()-7 will return you date 7 days before with current time. any comparison with this value will exclude all records that happened on the 7th previous day with time less than current time. so use like

DATEADD(dd,DATEDIFF(dd,0,getdate())-7,0)

instead of just
getdate()-7

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-18 : 04:41:29

quote:
Originally posted by rds207

Could you please elloborate this,
for eg i am running job at 2010-03-17 18:23:00,

i will get records between 2010-03-17 18:23:00 and 2010-03-10 18:23:00 right??

what are the records i am missing here?

do you want to take the time into consideration ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:05:27

quote:
Originally posted by rds207

Could you please elloborate this,
for eg i am running job at 2010-03-17 18:23:00,

i will get records between 2010-03-17 18:23:00 and 2010-03-10 18:23:00 right??

what are the records i am missing here?

quote:
Originally posted by visakh16

quote:
Originally posted by rds207

I usually write getdate()-7 to get past 7 days worth of data,
how do i get , last 5 days worth of data delete last 2 days of days,
basically i dont want last 2 days of data...

please keep in mind that getdate() also return time. so taking getdate()-7 will return you date 7 days before with current time. any comparison with this value will exclude all records that happened on the 7th previous day with time less than current time. so use like

DATEADD(dd,DATEDIFF(dd,0,getdate())-7,0)

instead of just
getdate()-7

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

If thats your requirement, then you're good to go with this. but if you want to consider those record which occured on 2010-03-17 before 18:23 then you need to strip off time part

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources