troubleshooting Question

Dates

Avatar of Mark Wilson
Mark Wilson asked on
Microsoft SQL Server 2008SQL
2 Comments1 Solution218 ViewsLast Modified:
I have been using the following date ranges in queries for several months now, using SQL 2008 R2

declare @range varchar(20), @start datetime, @end datetime

if @range='Today'
   select @start= floor(CONVERT(float,getdate())), @end=getdate()
else if @range='Yesterday'
   select @end= floor(CONVERT(float,getdate())), @start=@end-1
else if @range='Last 5 Days'
   select @end= floor(CONVERT(float,getdate())), @start=@end-5
else if @range='Last 7 Days'
   select @end= floor(CONVERT(float,getdate())), @start=@end-7
else if @range='Month To Date'
   select @end=GETDATE(), @start= DATEADD(d,1-DAY(getdate()),floor(CONVERT(float,getdate())))
else if @range='Last Month'
   select @end= DATEADD(d,1-DAY(getdate()),floor(CONVERT(float,getdate()))), @start=DATEADD(m,-1,@end)

The query is usually

select etc
from ect
where fielddate between @start and @end

My questions is in two parts

- Is the way I am doing correct/efficient or is there a better way of doing this?
- I also need the date ranges for Last Full Week - in this case Mon - Sun, Last Full Year, Year to Date

Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros