Need help with sorting by date in T-SQL

Hi Experts,
I need help with this query.  I need to sort this query by the CallDate column which is of Varchar datatype, not Datetime.  When I try to sort by CallDate descending, it's not sorting properly.  I believe the issue is that the column is of data type varchar.  How can I convert the column to date so that it sorts properly?  I have included my T-SQL code, and also what my query is currently returning (which is wrong).  

Thanks in advance for your help.
mrotor

Here's my code sample:
DECLARE @AgentID varchar(50),	@StartDate DateTime, @EndDate DateTime
SET @AgentID = '456'
SET @StartDate = '9/1/2013'
SET @EndDate = '9/16/2013'

SELECT AgentID,CallDate
FROM CALL_TABLE 
WHERE CallDate >= @StartDate AND CallDate <= @EndDate
AND AgentID = @AgentID
ORDER BY CallDate DESC

Open in new window



I GET THE FOLLOWING RESULTS (which is not sorting properly by CallDate descending)
AgentID            CallDate
456            9/8/2013
456            9/4/2013
456            9/3/2013
456            9/12/2013
456            9/10/2013
mainrotorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>CallDate column which is of Varchar datatype, not Datetime.
What genius made that decision?

Yep, it'll do text sorting not date sorting, because it's in a text field.

>How can I convert the column to date so that it sorts properly?
Try this.
SELECT AgentID,CallDate
FROM CALL_TABLE 
WHERE CallDate >= @StartDate AND CallDate <= @EndDate
AND AgentID = @AgentID
ORDER BY CAST(CallDate as datetime) DESC 

Open in new window

Warning: Casting calldate as datetime requires that all values are good date values.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Testing in SSMS..
CREATE TABLE #foo (dt varchar(15))

INSERT INTO #foo (dt) 
VALUES 
('2013-01-01'), ('2013-02-01'), ('2013-03-01'),
('2013-01-11'), ('2013-02-11'), ('2013-03-11'),
('2013-01-02'), ('2013-02-02'), ('2013-03-02')

sELECT * FROM #foo 
ORDER BY CAST(dt as datetime)

Open in new window

0
PortletPaulfreelancerCommented:
as I don't work with dates as varchar (luckily) I'm curious: Will that between be reliable?
just a thought, it may be fine, but I'd be checking it.

no points please (not that you'd be tempted to as it's not the answer :)

{+ edit}
actually I really would be checking it, there is an implicit type conversion as you are comparing a varchar (CallDate) to 2 datetime variables.  So, either

a. the datetime's are being implicitly converted to varchar (but what format?, the 'default'?)
or
b. the CallDate is being implicitly converted to datetime, where you may get quality of data issues

Don't follow why dates are stored this way (except in a staging table perhaps)
0
Scott PletcherSenior DBACommented:
Given that's it varchar, you will inevitably get non-date data in that column at some point.

Therefore, rather than CASTing to date, I'd force the format to 'YYYYMMDD' for ordering:

ORDER BY RIGHT('0' + LEFT(CallDate, CHARINDEX('/', CallDate+ '/') - 1), 2) + RIGHT('0' + REPLACE(SUBSTRING(CallDate, CHARINDEX('/', CallDate) + 1, 2), '/', ''), 2)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.