Casting a String to a DATETIME object in Access SQL 1

[mperreault]

I am in the midst of down-grading a web application I wrote for a previous employer. The previous application used SQL Server 2000 and I am having some trouble getting the app to work with MS Access’s flavor of SQL.

In my previous application I would cast 3 string fields to a datatime object which worked great on SQL Server 2000… Here is the SQL

SELECT Cast(DueDateYear + “-“ + DueDateMonth + “-“ + DueDateDay AS DATETIME) AS DueDate FROM tblProjects

I get the following error…

Microsoft OLE DB Provider for ODBC Drivers error ‘80040e14’

[Microsfot][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ‘Cast(DueDateYear + “-“ + DueDateMonth + “-“ + DueDateDay AS DATETIME)’.

I tried cutting and pasting the SQL statement into access’s query builder and I get the same error…

Syntax error (missing operator) in query expression ‘Cast(DueDateYear + “-“ + DueDateMonth + “-“ + DueDateDay AS DATETIME)’.

Any help on what the missing operator would be helpful…
Thanks!
-Matt

 

[PHV]

You may try this:
SELECT DateSerial(DueDateYear,DueDateMonth,DueDateDay) AS DueDate FROM tblProjects

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244