Find similar date values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find similar date values

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2009-06-03 : 10:53:39

In my database I have a 'lastmailed' field that contains dates + time looking like this:

2009-05-26 12:49:19.000
2009-04-29 11:49:19.000
2009-05-22 11:56:53.000

What I would like to be able to do is run a query that brings up all the same dates in the lastmailed field. I used this query but this isnt working in the way that I want as it is looking at the time part of the data too.

SELECT lastmailed,
COUNT(lastmailed) AS NumOccurrences
FROM wce_contact
GROUP BY lastmailed
HAVING ( COUNT(lastmailed) > 1 )

It would be great if somebody could tell me how to modify the query so that it only looks at the date part of the data eg:

2009-05-26
2009-04-29
2009-05-22

Hope i have explained myself clearly?

JT

SQLRatankalwa
Starting Member

42 Posts

Posted - 2009-06-03 : 11:08:22

SELECT Convert(Varchar(32), lastmailed, 103),
COUNT(Convert(Varchar(32), lastmailed, 103)) AS NumOccurrences
FROM wce_contact
GROUP BY Convert(Varchar(32), lastmailed, 103)
HAVING ( COUNT(Convert(Varchar(32), lastmailed, 103)) > 1 )

Ratan Kalwa
SQL Server Professionals User Group

http://www.revalsys.com

Topaz
Posting Yak Master

199 Posts

Posted - 2009-06-03 : 11:09:55

Awesome. Just what I needed. Thank you!

JT

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 11:10:03

As an approach:
dateadd(d,datediff(d,0,lastmailed),0)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 11:10:56

quote:
Originally posted by webfred

As an approach:
dateadd(d,datediff(d,0,lastmailed),0)

No, you're never too old to Yak'n'Roll if you're too young to die.

Too late - again.

No, you're never too old to Yak'n'Roll if you're too young to die.

Topaz
Posting Yak Master

199 Posts

Posted - 2009-06-03 : 11:14:47

Thanks anyway Fred

JT

Topaz
Posting Yak Master

199 Posts

Posted - 2009-06-03 : 11:17:17

In fact this query

SELECT Convert(Varchar(32), lastmailed, 103),
COUNT(Convert(Varchar(32), lastmailed, 103)) AS NumOccurrences
FROM wce_contact
GROUP BY Convert(Varchar(32), lastmailed, 103)
HAVING ( COUNT(Convert(Varchar(32), lastmailed, 103)) > 1 )

It brings up two fields one 'no column name' and the other 'NumOccurrences'

How can I give the 'no column name' a name so I can order it desc?

(Fred now is your chance to shine ;))

JT

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-04 : 02:51:46

SELECT Convert(Varchar(32), lastmailed, 103) as mailed,
COUNT(Convert(Varchar(32), lastmailed, 103)) AS NumOccurrences
FROM wce_contact
GROUP BY Convert(Varchar(32), lastmailed, 103)
HAVING ( COUNT(Convert(Varchar(32), lastmailed, 103)) > 1 )
ORDER BY mailed

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 03:12:57

See http://weblogs.sqlteam.com/peterl/archive/2009/03/18/A-general-approach-to-sort-different-dateformats-correctly.aspx

E 12°55'05.63"
N 56°04'39.26"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-06-04 : 09:44:00

Use webfred's dateadd/datediff solution for best efficiency.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources