Date sort issue - 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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Date sort issue

Author

Topic

Cachonga
Starting Member

2 Posts

Posted - 2008-02-06 : 12:56:52

I am using Cold Fusion and running the following SQL command -

SELECT DISTINCT LEFT(registerdate, 11) AS registerdate, COUNT(LEFT(registerdate, 11)) AS count
FROM jobseeker
WHERE (stateid IS NOT NULL)
GROUP BY LEFT(registerdate, 11)
ORDER BY registerdate

My report is ordered by mmm dd, yyyy, which has my Jan 2008 data appearing between my Feb 2008 data and Jun 2007 data. I woudl rather order this by yyyy/mm/dd. Maybe I've missed it somewhere, but I can't find how to do this in any of the documentation I've looked through! Any help would be greatly appreciated!

Thanks in advance!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-06 : 19:28:04

It looks like you're storing dates as strings. Fix this up and check out the DATEPART function.

Cachonga
Starting Member

2 Posts

Posted - 2008-02-07 : 09:05:52

Thanks for the lead. I tried DATEPART, but that didn't work very well. While looking up information on DATEPART, I stumbled upon CONVERT. The following query is what I came up with -

SELECT DISTINCT CONVERT(varchar, registerdate, 111) AS registerdate, COUNT(CONVERT(varchar, registerdate, 111)) AS count
FROM jobseeker
WHERE (stateid IS NOT NULL)
GROUP BY CONVERT(varchar, registerdate, 111)
ORDER BY registerdate

The results are exactly what I was looking for.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 09:19:19

COUNT(*) will be enough.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-07 : 10:08:07

Dont convert the date to varchars
If you want to omit time part, then use

dateadd(day,datediff(day,0,datecol),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources