Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

get distinct Dates from timestamp values

Posted on 2013-10-23
4
Medium Priority
?
326 Views
Last Modified: 2013-10-25
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
Comment
Question by:royjayd
  • 2
4 Comments
 
LVL 66

Accepted Solution

by:
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

Open in new window

0
 
LVL 66

Assisted Solution

by:Jim Horn
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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
ID: 39596196
no points please:

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

by:royjayd
ID: 39597940
thanks y'all
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question