Convert problem

I have a 'datetime' column and I want to select the count
of today's dates and insert into another table column with
integer type (Only the number as a count). I have the
following query:
Select count(*)from dbo.stats
where convert(varchar(12),accessdate, 112) = convert
(varchar(12),GETDATE(),112) AND accessdate <> ''
I get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'select count(*)
from dbo.stats
where convert(varchar(12),started, 112) = convert(varchar
(12),GETDATE(),112)
AND accessdate <> '' to a column of data type int.
How do I fix this problem.
Thanks.> Select count(*)from dbo.stats
--^
Missing space here?
> where convert(varchar(12),accessdate, 112) = convert
> (varchar(12),GETDATE(),112) AND accessdate <> ''
Did you mean
AND accessdate IS NOT NULL
? If it is a datetime column, it can't be equal to '' (empty string) since
it is *not* a string at all.
Also, if you want to make use of an index on the column, it would probably
be more efficient to say this:
WHERE accessdate >= {fn CURDATE()}
AND accessdate < DATEADD(DAY, 1, {fn CURDATE()})
AND accessdate IS NOT NULL
A|||The space is a typo error only in the post. Query works
even with '' alone (Without inserting) and the column is
not indexed column since there will be more than 1 value
for the same day............
>--Original Message--
>> Select count(*)from dbo.stats
>--^
>Missing space here?
>> where convert(varchar(12),accessdate, 112) = convert
>> (varchar(12),GETDATE(),112) AND accessdate <> ''
>Did you mean
>AND accessdate IS NOT NULL
>? If it is a datetime column, it can't be equal to ''
(empty string) since
>it is *not* a string at all.
>Also, if you want to make use of an index on the column,
it would probably
>be more efficient to say this:
>WHERE accessdate >= {fn CURDATE()}
> AND accessdate < DATEADD(DAY, 1, {fn CURDATE()})
> AND accessdate IS NOT NULL
>A
>
>.
>|||> The space is a typo error only in the post.
Then can you show your actual table structure, some sample data, and the
actual query you use (rather than transposing!!!!), then someone can try and
reproduce your error? The error actually doesn't fit with the query you've
shown; I'm guessing you are trying to execute this from EXEC or
sp_executeSQL.
> not indexed column since there will be more than 1 value
> for the same day...
Not sure what that has to do with indexing. Unique index, maybe. But you
can certainly index columns that contain duplicates, and you certainly
*should* consider indexing columns that will be used frequently in exact or
range queries, such as the one you are writing...|||Mike,
Maybe you are having problems with quotes. The error message suggests
that SQL-Server is interpreting your entire query as a varchar value.
Maybe you are building the query in a front-end tool and using single
quotes to delimited the SQL statement. In that case, you need to escape
the quotes of the statement so they end up in the final string.
Hope this helps,
Gert-Jan
Mike wrote:
> I have a 'datetime' column and I want to select the count
> of today's dates and insert into another table column with
> integer type (Only the number as a count). I have the
> following query:
> Select count(*)from dbo.stats
> where convert(varchar(12),accessdate, 112) = convert
> (varchar(12),GETDATE(),112) AND accessdate <> ''
> I get the following error:
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'select count(*)
> from dbo.stats
> where convert(varchar(12),started, 112) = convert(varchar
> (12),GETDATE(),112)
> AND accessdate <> '' to a column of data type int.
> How do I fix this problem.
> Thanks.