x
Solved

# get distinct Dates from timestamp values

Posted on 2013-10-23
Medium Priority
329 Views
hi guys
I have  a MODIFY_DATE timestamp column with values like this
MODIFY_DATE
2013-10-18 10:54:02.270
2013-10-18 10:54:02.270
2013-10-22 06:30:35.373
2013-10-23 16:51:14.790
2013-10-23 16:51:14.790

I am trying to get the Distinct Date from the column, my resultset should look like this
2013-10-18
2013-10-22
2013-10-23

what i have is
select distinct cast(MODIFY_DATE AS Date) as Date from CUSTOMER_TABLE
where  cast(MODIFY_DATE AS Date) = DATEADD(day, 0, convert(date, GETDATE()))
but this will get only todays date.

Any idea how i can acchieve this?

thanks
0
Question by:royjayd
• 2

LVL 66

Accepted Solution

Jim Horn earned 1200 total points
ID: 39595618
Worked for me without the WHERE clause..
``````CREATE TABLE #foo (MODIFY_DATE datetime)

INSERT INTO #foo (MODIFY_DATE)
VALUES
('2013-10-18 10:54:02.270'),
('2013-10-18 10:54:02.270'),
('2013-10-22 06:30:35.373'),
('2013-10-23 16:51:14.790'),
('2013-10-23 16:51:14.790')

select distinct cast(MODIFY_DATE AS Date) as Date
from #foo
``````
0

LVL 66

Assisted Solution

Jim Horn earned 1200 total points
ID: 39595621
>where  cast(MODIFY_DATE AS Date) = DATEADD(day, 0, convert(date, GETDATE()))
What were you trying to accomplish with the above statement?
DateAdd(day, 0, any date) will equal any date.
0

LVL 50

Assisted Solution

PortletPaul earned 400 total points
ID: 39596196

yep, all you need to do is remove the where clause

>> where  cast(MODIFY_DATE AS Date) = DATEADD(day, 0, convert(date, GETDATE()))
>> but this will get only todays date.
because that's what you just asked for :)

cast(MODIFY_DATE AS Date)  also could be: convert(date,Modify_date)
convert(date, GETDATE()) also could be: cast(getdate() AS Date)

so, your where clause could be rewritten as:

where cast(MODIFY_DATE AS Date)  = cast(getdate() AS Date)

i.e. only where that field = today
0

Author Comment

ID: 39597940
thanks y'all
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.