question

sqlLearner 1 avatar image
sqlLearner 1 asked

How to elminate seconds

I have an interesting question. I am trying to count the distinct dates from a table. The database logs the exact date and time of the report. So when I select distinct date it keeps the duplicates with varying seconds. For example these are two the same dates which I want counted as 1 but it counts it as 2 because the seconds differ. Any idea how to handle this? 2008-10-18 00:56:00 2008-10-18 23:51:00
sqldatetime
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image Mark commented ·
What version of SQL are you using? There is a special date data type in 2008.
3 Likes 3 ·
Oleg avatar image Oleg commented ·
@sqlLearner Mark is correct, date could be used in place of the older datetime when the time information is not relevant, so select distinct cast(DateFieldNameHere as date) will also do the trick, but only if you have SQL Server 2008 or better.
2 Likes 2 ·
ThomasRushton avatar image
ThomasRushton answered
SELECT DISTINCT Convert(varchar(10), DateFieldNameHere, 120) FROM ... should do it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
SELECT DISTINCT Convert(smalldatetime, DateFieldNameHere) Smalldatetime uses 5 bytes instead of 10 bytes for a char(10) and the precision is a minute
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ni10 avatar image
ni10 answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image
GPO answered
In the above example it is the hours and minutes that differ, not the seconds. The question seems to me to be asking "How do you strip the time off a datetime?" If you can't cast as a date see this: http://goo.gl/l17sn
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.