select last 7 days - 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

SQL Server 2005 Forums

Transact-SQL (2005)

select last 7 days

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-22 : 09:01:02

Hi all..
Once again here for your kindly help...
one user will be allowed only 30 days in the application..In the last 7 days mails will send him..
If the user modified his account then another 30 days will be granted.It is stored in modified date column...

I am having a table like this

id.....created_date.....Modified_date
1........28-9-2010...... NULL
2........19-9-2010.......10-10-2010
3........25-9-2010...... NULL
4........22-8-2010......26-9-2010

Now my need is find the users who are in the last 7 days of their activation days..Including the modified date..30 days are calculated using the created_date column if modified column is null

[bold]OUTPUT[/bold]
id
1
3
4

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-22 : 10:22:04

Try

I am assuming that both Created_date and Modified_date datatype is datetime.

If you are using varchar then let us know.

Select id, Datediff(dd,isnull(Modified_date,Created_date),Getdate()) <= 30 from yourtable

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-22 : 10:42:38

This?

select id from table
where datediff(d,getdate(),dateadd(d,30,isnull(modified_date,created_date))) <=7

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources