Solved: Convert Varchar to Date format

[Jarlisle]

I have a really crappy database where one column is a date, but it's not formatted as a date (not sure what the format is, but I think it's varchar). I want to use SQL code to format it as a date when I pull the query so it will sort correctly. The following is my query that doesn't work: The red part is the field that needs to be a date.


SELECT agent.ssno, WS_TRACKER.AGENT, agent.Supervisor, convert(varchar,WS_TRACKER.DATE_ENT,101), WS_TRACKER.Bpartner, WS_TRACKER.CUS_NAME, WS_TRACKER.IO_NUM, WS_TRACKER.WS_CITY, convert(varchar(10),WS_TRACKER.WS_DATE,101) AS 'Workshop_Date', WS_TRACKER.CERT_TYPE
FROM STRATADIAL.dbo.agent agent, STRATADIAL.dbo.WS_TRACKER WS_TRACKER
WHERE WS_TRACKER.AGENT = agent.name AND ((WS_TRACKER.DATE_ENT>={ts '2009-10-01 00:00:00'}))
ORDER BY WS_TRACKER.WS_DATE, WS_TRACKER.DATE_ENT, WS_TRACKER.CERT_TYPE

[Bob Phillips]

Doesn't SQL Server do an implicit conversion if it is a valid date?

[Jarlisle]

I don't know, but when I run the query it does not sort correctly.

[stanl]

seems if it is a timestamp there is no need for convert() as that will screw up a sort. What if you just use



SELECT agent.ssno, WS_TRACKER.AGENT, agent.Supervisor,WS_TRACKER.DATE_ENT, WS_TRACKER.Bpartner, WS_TRACKER.CUS_NAME, WS_TRACKER.IO_NUM, WS_TRACKER.WS_CITY, WS_TRACKER.WS_DATE, WS_TRACKER.CERT_TYPE
FROM STRATADIAL.dbo.agent agent, STRATADIAL.dbo.WS_TRACKER WS_TRACKER
WHERE WS_TRACKER.AGENT = agent.name AND ((WS_TRACKER.DATE_ENT>='10/1/2009'))
ORDER BY WS_TRACKER.WS_DATE, WS_TRACKER.DATE_ENT, WS_TRACKER.CERT_TYPE


.02 Stan