Solved

Dates

Posted on 2011-09-08
2
192 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:halifaxman
  • 2
2 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 500 total points
ID: 36501203
I'm confused as to why you are converting the dates to a floor.
Why aren't you treating date variables as date data types?

There are some examples of getting date values/ranges here
http://blog.sqlauthority.com/2008/08/29/sql-server-few-useful-datetime-functions-to-find-specific-dates/
0
 
LVL 29

Expert Comment

by:QPR
ID: 36501206
umm I meant converting to a float not a floor
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question