469,604 Members | 2,318 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,604 developers. It's quick & easy.

Timestamp problem!

Hello Group,

I am having a really weird problem... Can anyone tell the difference
between Query 1 and Query 2 below? Why Query 2 excludes '2/28/2005'?
Many thanks!

create table a
(ApplicationID int
,timestamp datetime)

insert into a values(1111,'2/26/05 3:24')
insert into a values(2222,'2/25/05 19:03')
insert into a values(1111,'2/4/05 14:54')
insert into a values(2222,'2/28/05 23:10')

--Query 1--
select year(timestamp) app_year
,month(timestamp) app_month
,day(timestamp) app_day
,count(distinct applicationid) numapps
from a
where year(timestamp)=2005 and month(timestamp)=2
group by year(timestamp)
,month(timestamp)
,day(timestamp)
order by year(timestamp)
,month(timestamp)
,day(timestamp)

--Query 2--
select year(timestamp) app_year
,month(timestamp) app_month
,day(timestamp) app_day
,count(distinct applicationid) numapps
from a
where timestamp between '2/1/2005' and '2/28/2005'
group by year(timestamp)
,month(timestamp)
,day(timestamp)
order by year(timestamp)
,month(timestamp)
,day(timestamp)

Jul 23 '05 #1
2 5010
'2/28/2005' represents midnight on that date whereas the DATETIME you
have in the database is after midnight therefore the BETWEEN predicate
will exclude it.

It's generally more convenient to use >= and < rather than BETWEEN:

....
WHERE x >= '20050201'
AND x < '20050301'

For date literals without times, use the ISO YYYMMDD format. Regional
formats like the one you used will fail under some server and
connection settings.

Don't use TIMESTAMP as a column name. It's a reserved word and anyway
is not a very meaningful name for a column.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
Thanks David, that's a big help!
David Portas wrote:
'2/28/2005' represents midnight on that date whereas the DATETIME you
have in the database is after midnight therefore the BETWEEN predicate will exclude it.

It's generally more convenient to use >= and < rather than BETWEEN:

...
WHERE x >= '20050201'
AND x < '20050301'

For date literals without times, use the ISO YYYMMDD format. Regional
formats like the one you used will fail under some server and
connection settings.

Don't use TIMESTAMP as a column name. It's a reserved word and anyway
is not a very meaningful name for a column.

--
David Portas
SQL Server MVP
--


Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Ben | last post: by
6 posts views Thread by Robert Schuldenfrei | last post: by
1 post views Thread by Roger Twomey | last post: by
reply views Thread by presley2 | last post: by
5 posts views Thread by pankaj_wolfhunter | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.