Convert nvarchar to smalldatetime

HI folks

I have tried to search for this but no luck so far.

I have a table that stores a date in the format dd/yy/yyyy but it is a nvarchar(50) instead of, for example, smalldatetime. The column is also named date.

I am unable to change the column to a smalldatetime because there are a large number of Classic ASP pages linking to this and it would mean a lot of work.

OK my problem is there is some SQL like:

SELECT, t.course, t.title, MAX(d.[date]) AS date FROM tbl_training t LEFT OUTER JOIN tbl_training_dates d ON = d.event_id WHERE t.cat_id = 3 AND t.visible = 1 AND > getdate() GROUP BY, t.course, t.title ORDER BY t.course

Obviously this doesnt work because the date column isnt actually a date. I’ve been trying to change the MAX(d.[date]) to something like:

MAX(CAST( AS smalldatetime))

…but still get an error on my ASP page that says:

[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.

Any ideas how to successfully convert this properly? Thanks

what version of SQL server you use. if you use 2008 r2 try this function CONVERT
CAST and CONVERT (Transact-SQL)