How can I get dates without hours or minutes in a SQL query to use a COUNT
? I need you to show me the repeated amount and the day it was made. Any suggestion? Be careful, it's not MySQL I need, it's for SQL server 2008 R2
SELECT TOP 2000
a.UserID,
(a.CharName) as maton,
(a.DeadCharName) as Victima,
(b.[level])as nivel_de_Victima,
COUNT (a.DeadCharName) As Veces,
a.actiontime <<---- Campo Fecha a selecionar
FROM PS_GameLog.dbo.Kill_Log a
INNER JOIN PS_GameData.dbo.Chars b
on a.CharID = b.CharID
WHERE a.CharID = 62336
ORDER BY Veces DESC
GROUP BY a.UserID,a.CharName,a.DeadCharName,b.[level]
ORDER BY Veces DESC
Example, of what format dates are in, I need to take the obvious 11 digits including hyphens.
2016-12-23 15:42:29.000
2016-12-23 15:42:28.000
2016-12-23 15:42:30.000
If you have SQL Server 2008+, you can convert it to
DATE
:If not, you can convert it to a string containing only the year, month, and day:
To convert the date field to format
YYYY-MM-DD
in SQL Server 2008 R2, you can use the functionCONVERT
with the following parameters:Demo
If the format you want is rather
DD-MM-YYYY
, then it would be this way:Demo
If you simply need the date portion without the time in order to make a count, then the format of the field doesn't matter. In this case, the simplest thing is to just make a
CAST
typeDATE
:Demo
In an older version of SQL (where it doesn't work
CONVERT(DATE,a.actiontime)
), I've used:I pass the date to FLOAT, then I find its integer part (thus removing the time), finally it becomes a date again.