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.id, t.course, t.title, MAX(d.[date]) AS date FROM tbl_training t LEFT OUTER JOIN tbl_training_dates d ON t.id = d.event_id WHERE t.cat_id = 3 AND t.visible = 1 AND d.date > getdate() GROUP BY t.id, 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(d.date 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