Why did I get zero records out?

[jinglin]

I have a table in my SQL server database. I did the sql as follows, which returns nothing. Can you help me find out why?

select count(distinct Logon_user) as countof
from Login
where ('9/1/2004'=convert(datetime,Date_Added,110))

Note: Date_Added field has a datetime type


Thanks a lot! Jinglin

 

[pkailas]

this is because the datetime column contains the time as well as the date. I assume you are trying to just extract the date portion?

you could try a where clause of

where Date_Added >= '9/1/2004' And Date_Added < '9/2/2004'

 

[checkai]

try this...

select count(distinct Logon_user) as countof
from Login
where ('9/1/2004'=convert(varchar,Date_Added,101))

 

[rac2]

You may wish to look at the values obtained by
convert(datetime,Date_Added,110) with

SELECT TOP 10 convert(datetime,Date_Added,110)
FROM Login

This will probably display dates that look like

2004-10-06 13:02:49.777 instead of 10/6/2004.

Try

select count(distinct Logon_user) as countof
from Login
where ('09/01/2004'=convert(CHAR(10),Date_Added,101))

 

[jinglin]

Thanks ALL
But still nothing coming out. I only need date, not time

 

[pkailas]

Umm.... your original question and query would only return the number or records found not any dates. My example will return the number of records found for the date of 9/1/2004. What are you really trying to retrieve?

 

[jinglin]

I want to count how many records there are in the table where date_added is 9/1/2004.
Thanks for your help!

 

[pkailas]

Then the following should work for you:

select count(distinct Logon_user) as countof
from Login
where Date_Added >= '9/1/2004' And Date_Added < '9/2/2004'

Since the datetime data type stores both the date and the time, there is no real way of stripping off the time. It's best to query the range. In this case, anthing greater than or equal to 9/1/2004 00:00:00 and less than 9/2/2004 00:00:00 which would be the 24 hour period of 9/1/2004

 

[jinglin]

I got it! Thanks a lot!