Morning
I am trying to return the distinct dates of an outcome by a unique identifer.
For example:
ID|Date
1 | 2011-10-01 23:57:59
1 | 2011-10-01 23:58:59
1 | 2010-10-01 23:59:59
2 | 2010-09-01 23:59:59
2 | 2010-09-01 23:58:29
3 | 2010-09-01 23:58:39
3 | 2010-10-01 23:59:14
3 | 2010-10-01 23:59:36
The times are not important just the dates. So for example on ID 1 I can't do a distinct on the ID as that would return only one of my dates. So I would want to return:
1|2011-10-01
1|2010-10-01
I Have tried the following query:
Drop Table #Temp
select Distinct DateAdd(dd, DateDiff(DD,0, Date),0) as DateOnly
,ID
Into #Temp
From Table
Select Distinct (Date)
,ID
From #Temp
I am getting the following results however:
ID|Date
1 | 2011-10-01 00:00:00
1 | 2011-10-01 00:00:00
1 | 2010-10-01 00:00:00
I'm new to SQL so apologies I may have made a glaring mistake. I have got so far by searching through the previously asked questions.
As always any help and pointers is greatly appreciated.
You can use the T-SQL convert function to extract the Date.
Try
CONVERT(char(10), GetDate(),126)
so, in your case, do
Drop Table #Temp
select Distinct CONVERT(char(10), DateAdd(dd, DateDiff(DD,0, Date),0), 126) as DateOnly
,ID
Into #Temp
From Table
Select Distinct (Date)
,ID
From #Temp
further informations: Getting the Date Portion of a SQL Server Datetime field
hope this helps!
If you are using Sql Server 2008 - you can cast DateTime column to a built in Date type , otherwise to get rid of time you should cast to VARCHAR() only day/month/year parts and then convert back to datetime so time part would be zeroed:
declare @dates table(id int, dt datetime)
INSERT INTO @dates VALUES(1, '2011-10-01 23:57:49')
INSERT INTO @dates VALUES(2, '2011-10-02 23:57:59')
INSERT INTO @dates VALUES(2, '2011-10-02 23:57:39')
SELECT stripped.id, stripped.dateOnly
FROM
(
-- this will return dates with zeroed time part 2011-10-01 00:00:00.000
SELECT id,
CONVERT(datetime,
CAST(YEAR(dt) as VARCHAR(4)) + '-' +
CAST(MONTH(dt) AS VARCHAR(2)) + '-' +
CAST(DAY(dt) AS VARCHAR(2))) as dateOnly
FROM @dates
) stripped
GROUP BY stripped.id, stripped.dateOnly
