By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,883 Members | 955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,883 IT Pros & Developers. It's quick & easy.

Timestamp problem!

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
'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

P: n/a
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.