December 2, 2014 at 1:56 am
True, I was just demonstrating why people sometimes forget to declare the length of the VARCHAR in a declare statement, because they are used to using the CONVERT/CAST method without a length declaration, without seeing any noticeable effects like the truncation when you create the variable using a DECLARE.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 3, 2014 at 12:01 pm
BWFC (11/27/2014)
You're welcome.One other thing, be careful using BETWEEN for date range queries. You're usually better using
where[Date] >= @startdate
and
[Date] <= @enddate
Have a look at this article which explains better than I can.
Neil
Typo here I believe: you want to use >= and <, rather than <=, on dates/datetimes.
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".
December 4, 2014 at 1:36 am
ScottPletcher (12/3/2014)
BWFC (11/27/2014)
You're welcome.One other thing, be careful using BETWEEN for date range queries. You're usually better using
where[Date] >= @startdate
and
[Date] <= @enddate
Have a look at this article which explains better than I can.
Neil
Typo here I believe: you want to use >= and <, rather than <=, on dates/datetimes.
Why would using < be the method to choose? If you want to include events that took place on the end date surely it makes sense to use <=?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
December 4, 2014 at 7:56 am
BWFC (12/4/2014)
ScottPletcher (12/3/2014)
BWFC (11/27/2014)
You're welcome.One other thing, be careful using BETWEEN for date range queries. You're usually better using
where[Date] >= @startdate
and
[Date] <= @enddate
Have a look at this article which explains better than I can.
Neil
Typo here I believe: you want to use >= and <, rather than <=, on dates/datetimes.
Why would using < be the method to choose? If you want to include events that took place on the end date surely it makes sense to use <=?
When you are using a datetime, the time stamp will be outside of the between predicate values. Therefore, you use less than the day after the end date, e.g. @date >= '2014-11-01' and @date < '2014-12-01'
Don Simpson
December 4, 2014 at 8:27 am
I'm really not following you here.
declare @startdate datetime = '2014-12-04'
declare @enddate datetime = '2014-12-05'
create table #EventHeader(
HistoryIDint primary keynot null
,DateTimeHappened datetimenot null
)
insert into #EventHeader
select 100001, '2014-12-01' union all
select 100002, '2014-12-01' union all
select 100003, '2014-12-01' union all
select 100004, '2014-12-01' union all
select 100005, '2014-12-02' union all
select 100006, '2014-12-02' union all
select 100012, '2014-12-02' union all
select 100008, '2014-12-03' union all
select 100009, '2014-12-03' union all
select 100010, '2014-12-03'
select distinct
eh.HistoryID
from
#EventHeader eh
where
DatetimeHappened>= @startdate
and datetimehappened< @enddate
drop table #EventHeader
In the above example using < for the endate, nothing that happens on the 2nd appears.
declare @enddate datetime = '2014-12-05'
create table #EventHeader(
HistoryIDint primary keynot null
,DateTimeHappened datetimenot null
)
insert into #EventHeader
select 100001, '2014-12-01' union all
select 100002, '2014-12-01' union all
select 100003, '2014-12-01' union all
select 100004, '2014-12-01' union all
select 100005, '2014-12-02' union all
select 100006, '2014-12-02' union all
select 100012, '2014-12-02' union all
select 100008, '2014-12-03' union all
select 100009, '2014-12-03' union all
select 100010, '2014-12-03'
select distinct
eh.HistoryID
from
#EventHeader eh
where
DatetimeHappened>= @startdate
and datetimehappened<= @enddate
drop table #EventHeader
In this case, using <=, I get the results back for the 2nd. Granted I'm using dates only but so was the OP. Even with times included, it doesn't make sense to me to use < if you want to see something that happened at exactly @enddate. It's unlikely but not impossible. I'm not arguing here, just trying to get my head round it 🙂
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
December 4, 2014 at 8:42 am
BWFC (12/4/2014)
I'm really not following you here.
declare @startdate datetime = '2014-12-04'declare @enddate datetime = '2014-12-05'
create table #EventHeader(
HistoryIDint primary keynot null
,DateTimeHappened datetimenot null
)
insert into #EventHeader
select 100001, '2014-12-01' union all
select 100002, '2014-12-01' union all
select 100003, '2014-12-01' union all
select 100004, '2014-12-01' union all
select 100005, '2014-12-02' union all
select 100006, '2014-12-02' union all
select 100012, '2014-12-02' union all
select 100008, '2014-12-03' union all
select 100009, '2014-12-03' union all
select 100010, '2014-12-03'
select distinct
eh.HistoryID
from
#EventHeader eh
where
DatetimeHappened>= @startdate
and datetimehappened< @enddate
drop table #EventHeader
In the above example using < for the endate, nothing that happens on the 2nd appears.
declare @enddate datetime = '2014-12-05'create table #EventHeader(
HistoryIDint primary keynot null
,DateTimeHappened datetimenot null
)
insert into #EventHeader
select 100001, '2014-12-01' union all
select 100002, '2014-12-01' union all
select 100003, '2014-12-01' union all
select 100004, '2014-12-01' union all
select 100005, '2014-12-02' union all
select 100006, '2014-12-02' union all
select 100012, '2014-12-02' union all
select 100008, '2014-12-03' union all
select 100009, '2014-12-03' union all
select 100010, '2014-12-03'
select distinct
eh.HistoryID
from
#EventHeader eh
where
DatetimeHappened>= @startdate
and datetimehappened<= @enddate
drop table #EventHeader
In this case, using <=, I get the results back for the 2nd. Granted I'm using dates only but so was the OP. Even with times included, it doesn't make sense to me to use < if you want to see something that happened at exactly @enddate. It's unlikely but not impossible. I'm not arguing here, just trying to get my head round it 🙂
You are correct when using "date," but if timestamps come into play, then <= '2014-11-30' only includes the stroke of midnight on 11/30. If you want to capture activity that occurs during the day, where the time is recorded, then you need to use "<" the day after the end date, or include the end time (e.g. '2014-11-30 23:59:59.997').
Don Simpson
December 4, 2014 at 8:49 am
Thanks Don, I'm completely with you now. We do a lot of date\time queries here and most of the time we either include the time or cast values as dates to match the parameters as appropriate. I'm relatively new to the SQL game so I was trying to make sure I wasn't missing something fundamental.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
December 4, 2014 at 8:57 am
BWFC (12/4/2014)
ScottPletcher (12/3/2014)
BWFC (11/27/2014)
You're welcome.One other thing, be careful using BETWEEN for date range queries. You're usually better using
where[Date] >= @startdate
and
[Date] <= @enddate
Have a look at this article which explains better than I can.
Neil
Typo here I believe: you want to use >= and <, rather than <=, on dates/datetimes.
Why would using < be the method to choose? If you want to include events that took place on the end date surely it makes sense to use <=?
You advance the ending date by one day, then use <, as that is the most accurate even if the data type changes later.
For example, on a smalldatetime column, say you want to query all of 2014. My suggested approach would be:
>= '20140101' AND < '20150101'
How do you code that as <=?
>= '20140101' AND <= '20141231 23:59:59'
Ugh! And if the column changes to datetime, the code above then misses times between 23:59:59.000 and 23:59:59.997 (btw, not .999, which would round up to the next minute). So you have to find all those issues and go back into the code and re-code as:
>= '20140101' AND <= '20141231 23:59:59.997'
Then what happens if you change the column to datetime2? Same thing all over!
Whereas:
>= '20140101' AND < '20150101'
continues to work accurately for all those data types.
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".
December 4, 2014 at 9:18 am
Whereas:
>= '20140101' AND < '20150101'
continues to work accurately for all those data types.
+1
Don Simpson
December 5, 2014 at 1:12 am
It all makes total sense now. The biggest problem I had was missing the bit about setting the date one day forward. As soon as I spotted that it all became clear:blush:
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply