T SQL Query help with date ranges

Question

T SQL Query help with date ranges

Kazmerelda

SSCarpal Tunnel

Points: 4667
More actions
October 20, 2010 at 9:58 am

#225294

Hi there,
Firstly I would like to say hello, as this is my first post on here.
Please can I ask for some assistance on date ranges. I am writing some T SQL that someone would like to run for specific weekly dates. They want to be able to specify the weekly dates, but am having problems trying to put it into SQL.
So far I have this query:
select callref,costcenter, cust_name, logdate, closedate, resp_time/60 as 'resp_time', fix_time/60 as 'fix_time', pcdesc.info
from opencall
left join pcdesc
on opencall.probcode=pcdesc.code
where suppgroup = 'XXXX'
and costcenter in ('xxx.co.uk')
and year(date_time)='2009'
and month(date_time)='09'
and status <>'17'
order by info desc
So where it says year and month I would like it to have start and end date parameters instead. This is going to be run via a different reporting tool (not SSRS) so I need to get the right SQL to extract the data.
I believe I need to do a Declare but am making my brain melt trying to get the right combo (and it's only Weds...bad times!).
Many thank in advance for your help...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

hallidayd SSCrazy Points: 2490 More actions · Answer 1

Hello

In order to make use of an index.... AND date_time >= '20090901' AND date_time < '20091001'So those are your start and end times hard coded. Exactly how we parameterise these depends on how you are making the call.

EDIT - first time I've used grtr than and less than symbols on here: how can I override the html encoding? 😀

GSquared SSC Guru Points: 260824 More actions · Answer 2

hallidayd (10/20/2010)
Hello
In order to make use of an index.... AND date_time >= '20090901'
AND date_time < '20091001'So those are your start and end times hard coded. Exactly how we parameterise these depends on how you are making the call.
EDIT - first time I've used grtr than and less than symbols on here: how can I override the html encoding? 😀

Split it on multiple lines.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

hallidayd SSCrazy Points: 2490 More actions · Answer 3

GSquared (10/20/2010)
Split it on multiple lines.

Ha! Makes sense (although it would be nice if the validation checked the order of the symbols!). Thanks 🙂

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Blake

You will want to create a parameterized stored procedure along the lines of:

CREATE PROC [dbo].[Blake]

@Start DATETIME

AS

DECLARE @Syr VARCHAR(4)

DECLARE @Smon VARCHAR(2)

SET @Syr = DATEPART(yyyy,@Start)

SET @Smon = DATEPART(mm,@Start)

SET @Smon = RIGHT('0' + @Smon,2) -- if month in your table 2 characters that is '09'

SELECT @Syr,@Smon

/* Run as: dbo.Blake ''10/13/10''*/

and subsitute the @Syr for your '2009' in

and year(date_time)='2009'

and @Smon for your '09' in

and month(date_time)='09'

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

hallidayd SSCrazy Points: 2490 More actions · Answer 5

Best practice is be to remove the system functions on the data source columns if at all possible, which in this case it is.

Similarly, using a language independent date time format ensures your code can be run on any SQL Server, irrespective of configuration, anywhere in the world.

Ref:

http://www.karaszi.com/SQLServer/info_datetime.asp

WayneS SSC Guru Points: 95461 More actions · Answer 6

bitbucket-25253 (10/20/2010)
Blake
You will want to create a parameterized stored procedure along the lines of:
CREATE PROC [dbo].[Blake]
@Start DATETIME
AS
DECLARE @Syr VARCHAR(4)
DECLARE @Smon VARCHAR(2)
SET @Syr = DATEPART(yyyy,@Start)
SET @Smon = DATEPART(mm,@Start)
SET @Smon = RIGHT('0' + @Smon,2) -- if month in your table 2 characters that is '09'
SELECT @Syr,@Smon
/* Run as: dbo.Blake ''10/13/10''*/
and subsitute the @Syr for your '2009' in
and year(date_time)='2009'
and @Smon for your '09' in
and month(date_time)='09'

I'd suggest something just a little bit different (to remove the function calls in the where clause). Since we know we want to do all dates for a month:

DECLARE @ThisMonth datetime,

@NextMonth datetime;

SELECT @ThisMonth = DateAdd(month, 0, DateDiff(month, 0, GetDate())),

@NextMonth = DateAdd(month, 1, @ThisMonth);

And then, as hallidayd suggested, change the where clause to:

where suppgroup = 'XXXX'

and costcenter in ('xxx.co.uk')

and date_time >= @ThisMonth

and date_time < @NextMonth

and status <>'17'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 7

Hi All,

Thank you so much for your help, it's appreciated.

A good night's sleep and looking at this has made things much clearer. I will work on it today and let you know when it works.

Thanks again, Karen

WayneS SSC Guru Points: 95461 More actions · Answer 8

karen.blake (10/21/2010)
Hi All,
Thank you so much for your help, it's appreciated.
A good night's sleep and looking at this has made things much clearer. I will work on it today and let you know when it works.
Thanks again, Karen

No problem. Thanks for the feedback, and please do let us know how it's working out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2