Re: mssql query

On Mar 16, 2004, at 5:17 PM, php-db-digest-help@lists.php.net wrote:

From: Adam Williams <awilliam@mdah.state.ms.us> Date: March 16, 2004 7:32:57 AM PST To: php-db@lists.php.net 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