Hi to all. A field (TransferredDate) in a view I query has a data type of datetime.
I have a WHERE clause:
WHERE TransferredDate >= @StartDate AND TransferredDate <= @EndDate
If I set variables as below nothing is returned for 19 Nov 21. If I change end date to 20 Nov 2021 values for 19 Nov 2021 are returned. I am confused 🙂
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '18 Nov 2021'
SET @EndDate = '19 Nov 2021'
WHERE
[FileName] IS NOT NULL
AND [FileName] <> ''
AND TransferredDate >= @StartDate
AND TransferredDate <= @EndDate
GROUP BY NTUserName,ParsedUserName,TransferredDate,EPName,NTUserName,FileSize
As always help very much appreciated.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 27, 2022 at 5:54 pm
'19 Nov 2022' is converts to the datetime value of '19 Nov 2022 00:00:00.000', which is earlier (less than) '19 Nov 2022 15:19:12.000'.
Try:
WHERE [...]
AND CONVERT(date, TransferredDate) BETWEEN @StartDate AND @EndDate
[...]
Internally, the datetime date type is a complex type of two values: the date part and the time part. Performing a CONVERT from datetime value to a date type value simple means 'only look at the first internal column' and is one rare case where a function on the left side of a predicate does not break SARGability. Indexes still hold.
Eddie Wuerch
MCM: SQL
The best way is to use > the next day rather than <= the end day. Like this:
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '18 Nov 2021'
SET @EndDate = '19 Nov 2021'
WHERE
[FileName] IS NOT NULL
AND [FileName] <> ''
AND TransferredDate >= @StartDate
AND TransferredDate < DATEADD(DAY, 1, @EndDate) --<<--NOTE the "<" rather than "<="
GROUP BY NTUserName,ParsedUserName,TransferredDate,EPName,NTUserName,FileSize
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2022 at 7:13 pm
'19 Nov 2022' is converts to the datetime value of '19 Nov 2022 00:00:00.000', which is earlier (less than) '19 Nov 2022 15:19:12.000'.
Try:
WHERE [...]
AND CONVERT(date, TransferredDate) BETWEEN @StartDate AND @EndDate
[...]Internally, the datetime date type is a complex type of two values: the date part and the time part. Performing a CONVERT from datetime value to a date type value simple means 'only look at the first internal column' and is one rare case where a function on the left side of a predicate does not break SARGability. Indexes still hold.
Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some restrictions. Plus, again, it's a very bad approach more generally.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2022 at 8:24 pm
Thanks very much for your responses. I will amend what i have.
Point duly noted regards using functions in WHERE clause.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 27, 2022 at 8:25 pm
Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some restrictions. Plus, again, it's a very bad approach more generally.
I think you know that's NOT true. It's ok to use functions in the WHERE clause... just not on columns in the WHERE clause.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2022 at 9:00 pm
ScottPletcher wrote:Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some restrictions. Plus, again, it's a very bad approach more generally.
I think you know that's NOT true. It's ok to use functions in the WHERE clause... just not on columns in the WHERE clause.
Yes, I felt that was rather clear anyway since I used a function on a variable (constant value) myself in my own query example.
But, OK, it can't hurt to explicitly state it.
The point for the "no function on columns rule" is that functions on a column can prevent SQL from making best use -- including sometimes even any use -- of an index on a column(s) that could otherwise be used if the function weren't present.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 28, 2022 at 1:54 am
Jeff Moden wrote:ScottPletcher wrote:Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some restrictions. Plus, again, it's a very bad approach more generally.
I think you know that's NOT true. It's ok to use functions in the WHERE clause... just not on columns in the WHERE clause.
Yes, I felt that was rather clear anyway since I used a function on a variable (constant value) myself in my own query example.
But, OK, it can't hurt to explicitly state it.
The point for the "no function on columns rule" that functions on a column can prevent SQL from making best use -- including sometimes even any use -- on an index on a column(s) that could otherwise be used if the function weren't present.
Agreed... no arguments there.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2022 at 9:48 am
Thank you all for your expert input. As always greatly appreciated.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply