raymurphy
asked on
Ordering date output where date is in nvarchar column
Have an nvarchar(10) column (PERIOD_DATE) on my historytable, which holds dates in UK dd/mm/yy format. I use the following query to count how many entries we have for a specific
PERIOD_DATE :
SELECT DISTINCT PERIOD_DATE, count(*) AS HowMany
FROM historytable
GROUP BY PERIO_DATE
Query returns correct results, e.g :
PERIOD_DATE HowMany
31/12/05 261180
31/08/06 472998
31/10/08 668526
28/02/11 404151
31/07/10 526828
31/03/06 327388
31/03/11 384048
However, I'd like to order the results so that they appear in ascending date format, eg :
31/12/05 261180
31/03/06 327388
31/08/06 472998
31/10/08 668526
31/07/10 526828
28/02/11 404151
31/03/11 384048
How can I do this, even though the date value is in an nvarchar(10) column ?
PERIOD_DATE :
SELECT DISTINCT PERIOD_DATE, count(*) AS HowMany
FROM historytable
GROUP BY PERIO_DATE
Query returns correct results, e.g :
PERIOD_DATE HowMany
31/12/05 261180
31/08/06 472998
31/10/08 668526
28/02/11 404151
31/07/10 526828
31/03/06 327388
31/03/11 384048
However, I'd like to order the results so that they appear in ascending date format, eg :
31/12/05 261180
31/03/06 327388
31/08/06 472998
31/10/08 668526
31/07/10 526828
28/02/11 404151
31/03/11 384048
How can I do this, even though the date value is in an nvarchar(10) column ?
Last Comment
ASKER
Thanks for prompt reply, faiqa16 ....
Just tried that :
SELECT DISTINCT PERIOD_DATE, count(*) AS HowMany
FROM historytable
GROUP BY PERIOD_DATE
ORDER BY CONVERT (datetime, PERIOD_DATE,103)
But getting error of
ORDER BY items must appear in the select list if SELECT DISTINCT is specified ??
Just tried that :
SELECT DISTINCT PERIOD_DATE, count(*) AS HowMany
FROM historytable
GROUP BY PERIOD_DATE
ORDER BY CONVERT (datetime, PERIOD_DATE,103)
But getting error of
ORDER BY items must appear in the select list if SELECT DISTINCT is specified ??
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That did the trick, so thanks for your help ,,,,
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
SELECT
FROM
GROUP BY
ORDER BY CONVERT (datetime, PERIOD_DATE,103)
check the style number you need here:
http://msdn.microsoft.com/en-us/library/ms187928.aspx