confused about date values

I tried running this query :
select count(*) from table1 where dateadded > 1/1/2004
First of all, it worked. I didnt expect to 'cos i didnt put the quotes
around the date.
Secondly it returned a different result set than when I did include the date
select count(*) from table1 where dateadded > '1/1/2004'
CreateDate is a smalldatetime datatype.
Can someone explain this behaviour? Using SQL 2K> select count(*) from table1 where dateadded > 1/1/2004
This is equivalent to:
select count(*) from table1 where dateadded > 0
because 1/1/2004 is evaluated as an arithmetic expression without the
quotes.
The best way to specify date constants is in yyyymmdd format so that the
value is interpreted consistently regardless of the data format settings:
select count(*) from table1 where dateadded > '20040101'
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uKKBORXjGHA.4776@.TK2MSFTNGP05.phx.gbl...
>I tried running this query :
> select count(*) from table1 where dateadded > 1/1/2004
> First of all, it worked. I didnt expect to 'cos i didnt put the quotes
> around the date.
> Secondly it returned a different result set than when I did include the
> date
> select count(*) from table1 where dateadded > '1/1/2004'
> CreateDate is a smalldatetime datatype.
> Can someone explain this behaviour? Using SQL 2K
>|||Single quotation marks must be placed around all char, nchar, varchar,
nvarchar, text, datetime, and smalldatetime data.
As Mr. Guzman stated not following this practice in regards to a date would
cause SQL to evaluate the string as an equation.
"Hassan" wrote:

> I tried running this query :
> select count(*) from table1 where dateadded > 1/1/2004
> First of all, it worked. I didnt expect to 'cos i didnt put the quotes
> around the date.
> Secondly it returned a different result set than when I did include the da
te
> select count(*) from table1 where dateadded > '1/1/2004'
> CreateDate is a smalldatetime datatype.
> Can someone explain this behaviour? Using SQL 2K
>
>