Convert Date in SQLServer and Access

Question

Is there a function that works in both MS Access and SQLServer 2k5+ that will convert a string to a date? It seems CDate works in access and Convert works in SQLServer, but I'd like a function that works in both.

Thanks!

Solution 1

It is important to remember that SQL and Access are not running on the same platform and you will not have access to the same functions in each platform. This is like asking if you can use an echo command in ASP.NET. Echo works in PHP but not .NET languages. I do not believe you will find a common function you can use.

I would however consider simply storing the value as a date so no conversion needs to occur in the first place.

in SQL...

cast (MyColumn as datetime)

Access...

cdate(MyColumn)

Further reading:

1. MSDN: SQL
2. Tech on the net: Access CDATE

Solution 2

MS Access and Sql Server have use different platform and there have no common function for convert date. But can you use normal query without convert date on server side and then can you convert that date on programming side as you want desire format.