PDA

View Full Version : Solved: Convert Varchar to Date format



Jarlisle
11-20-2009, 05:46 PM
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
11-21-2009, 10:56 AM
Doesn't SQL Server do an implicit conversion if it is a valid date?

Jarlisle
11-21-2009, 05:23 PM
I don't know, but when I run the query it does not sort correctly.

stanl
11-29-2009, 07:25 AM
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