How to write a query using the field dtRequested which is defined as type varchar using SQL Server 2005?

I am trying to run a query against a SQL Server 2005 table that has a field titled dtRequested defined as varchar(500) with values such as:

8/8/2005
May 24 2005 12:00AM

Do you know how I could write a query using a WHERE clause in which I can retrieve
records where dtRequested is > 12/31/2011. In other words, I want records with dates
for years 2012 and 2013 only.

I tried the following query but it doesn't pull in records with dtRequested values greater than '12/31/2011'.

My query is as follows:

SELECT dev AS Developer, userNameF + ' ' + userName AS 'User', appname AS 'Application Name', dtRequested AS 'Date Requested', changeRequest AS Request, reason AS Reason,
tSOXSignOFF.dtUsrSignOff
FROM tSOXUsrRequest LEFT OUTER JOIN tSOXSignOFF ON
tSOXUsrRequest.reqID = tSOXSignOFF.reqID
WHERE (tSOXSignOFF.usrSignOff='Yes') and devid='zimmermj'
and dtRequested > '12/31/2011'
zimmer9Asked:
Who is Participating?
 
Ron MalmsteadConnect With a Mentor Information Services ManagerCommented:
SELECT dev AS Developer, userNameF + ' ' + userName AS 'User', appname AS 'Application Name', dtRequested AS 'Date Requested', changeRequest AS Request, reason AS Reason,
tSOXSignOFF.dtUsrSignOff
FROM tSOXUsrRequest LEFT OUTER JOIN tSOXSignOFF ON
tSOXUsrRequest.reqID = tSOXSignOFF.reqID
WHERE (tSOXSignOFF.usrSignOff='Yes') and devid='zimmermj'
and CAST(dtRequested as DateTime) > '12/31/2011'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.