On Mar 16, 2004, at 5:17 PM, php-db-digest-help@xxxxxxxxxxxxx wrote:
From: Adam Williams <awilliam@xxxxxxxxxxxxxxxx> Date: March 16, 2004 7:32:57 AM PST To: php-db@xxxxxxxxxxxxx Subject: mssql query
I've used mysql a little, but now I'm working with a MS SQL SERVER 2000
database. We have a proprietary application/hardware setup where
when a person enters the building, the time they enter the building
is automatically put into the database in a field called eventtime, and
their access card is entered in a field called cardnum. The
time entered is like the mysql NOW() function (i dunno what its called in
mssql), the format is YYYY-MM-DD HH:MM:SS.000 anyway. I need to write an
SQL query that will get a unique count of cardnum based on how many times
the person enters a building each day. So I need to do some sort of
UNIQUE (although in mssql I think its distinct) count on the YYYY-MM-DD
and ignore the HH:MM:SS.000, but looking at the mssql reference book I
have, it doesn't go into great detail on date functions, so I was
wondering if anyone knows how to do a unique on the eventtime with
the date and ignore the time.
MS SQL has a rich set of date functions - they may be a little hard to figure out but you can do everything.
I'd try:
SELECT count(*), id, convert(varchar, eventtime, 10)
FROM users
WHERE eventtime > '3/1/2004' /* just so you don't get the whole table */
GROUP BY id, convert(varchar, eventtime, 10)
The trick is convert(varchar, eventtime, 10) changes the datetime into a string and the last argument (10) is the format. If you pick a format without the seconds you can use GROUP BY.
The function equivalent to NOW() is getdate() so try these:
select convert(varchar, getdate(), 9)
select convert(varchar, getdate(), 10)
select convert(varchar, getdate(), 11)
select convert(varchar, getdate(), 12)
select convert(varchar, getdate(), 110) /* adding 100 is the same but with a 4 digit year */
Good luck, Frank
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
