DATEPART doesn't match BETWEEN... grrr

Hi,
I'm trying to report a months data, but the results differ when I use
DATEPART as opposed to BETWEEN
i.e. (The full queries are at the end of this post)
this...
where datepart(month,sessionstart) = 1
and datepart(year,sessionstart) = 2005
differs from this...
where sessionstart between '01/01/2005' and '01/31/2005'
In BOL, it states for between... "BETWEEN returns TRUE if the value of
test_expression is greater than or equal to the value of begin_expression an
d
less than or equal to the value of end_expression.", and yet if I change the
between statement to..
where sessionstart between '01/01/2005' and '02/01/2005'
I then get the same results as DATEPART.
Am I going loopy? (It does feel like it). I guess it may be pointing to an
issue in BOL.
Many thanks in advance,
Andy
The full queries... (which measure application usage in citrix metaframe )
--Query #1
select count(*) as 'Session count', lu_appname.appname as 'Published
Application'
from sdb_session
join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
where datepart(month,sessionstart) = 1
and datepart(year,sessionstart) = 2005
group by lu_appname.appname
order by lu_appname.appname
--Query #2
select count(*) as 'Session count', lu_appname.appname as 'Published
Application'
from sdb_session
join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
where sessionstart between '01/01/2005' and '01/31/2005'
group by lu_appname.appname
order by lu_appname.appnameAndy,
It could be because the datetime data type also include time part and when
you specify a constant like '01/31/2005', sql server is using '00:00:00.000'
as the time. Basically what you are doing is:
...
where sessionstart >= '2005-01-01T00:00:00.000' and sessionstart <=
'2005-01-31T00:00:00.00'
try this way:
...
where sessionstart >= '01/01/2005' and sessionstart < '02/01/2005'
AMB
"Andy" wrote:

> Hi,
> I'm trying to report a months data, but the results differ when I use
> DATEPART as opposed to BETWEEN
> i.e. (The full queries are at the end of this post)
> this...
> where datepart(month,sessionstart) = 1
> and datepart(year,sessionstart) = 2005
> differs from this...
> where sessionstart between '01/01/2005' and '01/31/2005'
> In BOL, it states for between... "BETWEEN returns TRUE if the value of
> test_expression is greater than or equal to the value of begin_expression
and
> less than or equal to the value of end_expression.", and yet if I change t
he
> between statement to..
> where sessionstart between '01/01/2005' and '02/01/2005'
> I then get the same results as DATEPART.
> Am I going loopy? (It does feel like it). I guess it may be pointing to an
> issue in BOL.
> Many thanks in advance,
> Andy
>
>
> The full queries... (which measure application usage in citrix metaframe )
> --Query #1
> select count(*) as 'Session count', lu_appname.appname as 'Published
> Application'
> from sdb_session
> join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
> where datepart(month,sessionstart) = 1
> and datepart(year,sessionstart) = 2005
> group by lu_appname.appname
> order by lu_appname.appname
> --Query #2
> select count(*) as 'Session count', lu_appname.appname as 'Published
> Application'
> from sdb_session
> join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
> where sessionstart between '01/01/2005' and '01/31/2005'
> group by lu_appname.appname
> order by lu_appname.appname
>|||How are the results different? And which gives you the proper resulst
(looking at sessionstart in the select clause) BETWEEN is rarely a good
thing to use with dates. what you are saying in your query is anything
between midnight of the first and midnight (the start of the day) on the
31st. Use:
sessionstart >= '20050101' and sessionstart < '20050201'
This will give you the full month (note you should also no use the date
format you are using, because it can be confusing, and has different meaning
in some situations, use YYYYMMDD and you will never have an issue. The
datepart thing will work, but you will not likely get good index usage.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:41013C4E-5432-4A35-94F2-1FD77FAC6B7D@.microsoft.com...
> Hi,
> I'm trying to report a months data, but the results differ when I use
> DATEPART as opposed to BETWEEN
> i.e. (The full queries are at the end of this post)
> this...
> where datepart(month,sessionstart) = 1
> and datepart(year,sessionstart) = 2005
> differs from this...
> where sessionstart between '01/01/2005' and '01/31/2005'
> In BOL, it states for between... "BETWEEN returns TRUE if the value of
> test_expression is greater than or equal to the value of begin_expression
> and
> less than or equal to the value of end_expression.", and yet if I change
> the
> between statement to..
> where sessionstart between '01/01/2005' and '02/01/2005'
> I then get the same results as DATEPART.
> Am I going loopy? (It does feel like it). I guess it may be pointing to an
> issue in BOL.
> Many thanks in advance,
> Andy
>
>
> The full queries... (which measure application usage in citrix metaframe )
> --Query #1
> select count(*) as 'Session count', lu_appname.appname as 'Published
> Application'
> from sdb_session
> join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
> where datepart(month,sessionstart) = 1
> and datepart(year,sessionstart) = 2005
> group by lu_appname.appname
> order by lu_appname.appname
> --Query #2
> select count(*) as 'Session count', lu_appname.appname as 'Published
> Application'
> from sdb_session
> join LU_APPNAME on sdb_session.FK_APPNAMEID = LU_APPNAME.PK_APPNAMEID
> where sessionstart between '01/01/2005' and '01/31/2005'
> group by lu_appname.appname
> order by lu_appname.appname
>|||Many thanks for that, date formatting here in the UK is always a pain due to
most products being US-Centric, so yyyy/mm/dd would seem to be a good idea.
It seems like using between is more trouble than its worth.
Thanks again to all who posted.
Andy
Louis Davidson" wrote:

> How are the results different? And which gives you the proper resulst
> (looking at sessionstart in the select clause) BETWEEN is rarely a good
> thing to use with dates. what you are saying in your query is anything
> between midnight of the first and midnight (the start of the day) on the
> 31st. Use:
> sessionstart >= '20050101' and sessionstart < '20050201'
> This will give you the full month (note you should also no use the date
> format you are using, because it can be confusing, and has different meani
ng
> in some situations, use YYYYMMDD and you will never have an issue. The
> datepart thing will work, but you will not likely get good index usage.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:41013C4E-5432-4A35-94F2-1FD77FAC6B7D@.microsoft.com...
>
>|||oh, I forgot to mention...
You have inferred that Datepart is not a good choice of syntax because of
bad index usage.
Is there a reason for this?
Many thanks - I know this is probably going of on a tangent...|||Google for "search arguments".
Transact-SQL Query
SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/transact_sql.asp
AMB
"Andy" wrote:

> oh, I forgot to mention...
> You have inferred that Datepart is not a good choice of syntax because of
> bad index usage.
> Is there a reason for this?
> Many thanks - I know this is probably going of on a tangent...
>|||the basic issue is that if you manipulate the value, then do comparisons to
it, SQL Server cannot then unmanipulate it and see if an index would help.
For your example:
where datepart(month,sessionstart) = 1
and datepart(year,sessionstart) = 2005
The index is on sessionstart, so it is ordered in a given way, so if the
optimizer knows what you are looking for is 20050101 <= sessionstart <
20050201, it is simple to do an index scan. The index is not ordered in
the order of the dateparts, so it cannot be used (except for a covering
query, if all you need is sessionstart, but then it would have to look at
all values of sessionstart)
Could it be built to do this? Probably, but only because you included the
year (the most significant factor in the index) but it would be more costly,
and there are common ways of writing these queries that work (as we have
been discussing)
And like Alejandro says,
"Google for 'search arguments'
Transact-SQL Query
SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/transact_sql.asp"
for lots more reading on the subject.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:30F05FEC-7BCD-495F-984E-9A12BBBFF881@.microsoft.com...
> oh, I forgot to mention...
> You have inferred that Datepart is not a good choice of syntax because of
> bad index usage.
> Is there a reason for this?
> Many thanks - I know this is probably going of on a tangent...
>|||On Tue, 1 Feb 2005 10:27:05 -0800, Andy wrote:

>Many thanks for that, date formatting here in the UK is always a pain due t
o
>most products being US-Centric, so yyyy/mm/dd would seem to be a good idea.
Hi Andy,
Though it might seem to be a good idea, it really isn't.
SQL Server supports many international date notations. That can be great,
but it can also be a pain - especially when SQL Server starts interpreting
date constants using a format you didn't intend it to. YYYY/MM/DD might
seem obvious, but there are countries where YYYY/DD/MM is used, and SQL
Server is aware of that...
The only formats that are truly unambiguous are:
* yyyymmdd (no slashes, dots, hyphens or other interpunction)
--> for date only (time part will default to midnight)
* yyyy-mm-ddThh:mm:ss.ttt (note the hyphens, colons, dot and capital T)
--> for date and time (the milliseconds part [.ttt] is optional)

>It seems like using between is more trouble than its worth.
This is absolutely true for date ranges..
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)