Problem running sql query on sql express

I am running a query on a few servers, it works fine on all SQL 2008 R2 and up. But when I ran it on an SQL Express 2005 I got an error.

This is the query:

SELECT ExpiresOn as originalValue,
    CONVERT(VARCHAR(25), CONVERT(DATE, ExpiresOn), 101) as newValue
    FROM Users
    WHERE ISDATE(ExpiresOn) = 1

This is the error:

Type DATE is not a defined system type

Is there a way to tweak the sql above so it works on 2005 ?   Meantime Ill advice my client to update their SQL.
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
The DATE data type was introduced to T-SQL with SQL Server 2008. So you cannot use it on SQL Server 2005 or lower.

So you need to convert it to DATETIME.

But upgrading is the better path.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
... and there is no real reason for keeping 2005 Express but the effort (which is not that much).
AleksAuthor Commented:
I agree. I already asked the client to upgrade to 2008R2

I heard it also doesn't work on 2012  ?  Is this true ?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
ste5anSenior DeveloperCommented:
Nope. The DATE data type still exists :)
arnoldCommented:
Since you are advising the upgrade route,..

However, if you want to maintain the current query functionality no matter what system you are on, you could use sp_addtype to define DATE as the data type matching DATETIME.
Not sp_addtype defines a user datatype on the server

https://msdn.microsoft.com/en-us/library/ms189784%28v=sql.90%29.aspx
AleksAuthor Commented:
Thanks everyone !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.