Stored Procedure on SQL2005/8 Express

Question

Post reply

Stored Procedure on SQL2005/8 Express

Viewing 8 posts - 16 through 22 (of 22 total)

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

nigel. SSChampion Points: 11636 More actions · Answer 1

Glad we could be of help.

But please keep in mind Elliot's warnings about comparisons with dates when truncating the times.

Nigel

Nigel
How to post questions

Tiaan Clarke Mr or Mrs. 500 Points: 502 More actions · Answer 2

Nigel & Elliot (I got your names right this time), I tried Nigel's second suggested bit of code.

Except that I changed it from subtracting 3 milliseconds, to subtracting 1 day from the current date.

Also changed the query in the stored proc to look for data BETWEEN those dates, not greater than & less than. If that makes sense at all.

Thanks to you both for your help.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I just wanted you to understand that even with a between operation you need to keep in mind time..

Start Date: 8/26/2009 00:00:00.000

End Date: 8/27/2009 00:00:00.000

Values: 8/26/2009 01:00:00.000 <-- Will be returned

Values: 8/26/2009 01:57:00.000 <-- Will be returned

Values: 8/26/2009 23:57:59.997 <-- Will be returned

Values: 8/27/2009 00:00:00.000 <-- Will be returned

Values: 8/27/2009 01:00:00.000 <-- Will NOT be returned

Values: 8/27/2009 01:57:00.000 <-- Will NOT be returned

Just wanted to be sure we understood each other..

CEWII

Tiaan Clarke Mr or Mrs. 500 Points: 502 More actions · Answer 4

Hi Elliot,

I see what you're saying. So it is in fact better to remove 3 milliseconds for the end date. Then to remove 1 month from the end date for the start date?

I hope I am reading this correctly. Will be better in this case?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

tiaanb (8/28/2009)
Hi Elliot,
I see what you're saying. So it is in fact better to remove 3 milliseconds for the end date. Then to remove 1 month from the end date for the start date?
I hope I am reading this correctly. Will be better in this case?

Actually, it would be better to do this:

...

SomeDate >= @StartDate and SomeDate = @StartDate and

st.DateCol < @EndDate;

[/code]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

I'm not sure about "better", but when times CAN come into play we need to be aware of the dates we choose. As far as taking 3ms off, I would rather see a <, I would do something to today's date like

DECLARE @EndDt datetime

SELECT @EndDt = CONVERT( varchar(10), GETDATE(), 101 )

I am taking advantage of implicit conversions, I take today's date and shave off the time, when I use that with = @StartDt

-- AND YourDateField = and a = is important because you want to be sure to include anything that occured at midnight not just the thing that happened 3ms later. Also if that date field is indexed either the >/< or between will operate very fast because it can easily tell what records to include because it only has to find the start and end and can return the rows between without additional searches.

I hope this is clear..

CEWII

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Avoid the coversion of datetime to char to datetime. If you want to strip off the time (ie set it to 00:00:00.000) this actually works faster (there has been numerous discussions and tests regarding this on SSC, you just have to search for the threads):

select dateadd(dd, datediff(dd, 0, getdate()), 0);

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

For single instances I'm thinking that doing it with the conversion involves very little impact on performance. I'm not disputing that this works. I did a really quick check to see if it even registered

SET STATISTICS TIME ON

GO

DECLARE @EndDt datetime

SELECT @EndDt = dateadd(dd, datediff(dd, 0, GETDATE()), 0);

GO

DECLARE @EndDt datetime

SELECT @EndDt = CONVERT( varchar(10), GETDATE(), 101 );

GO

Results:

SQL Server parse and compile time: