Correct datetime syntax for MSSQL?

[Ian Hinson]

Hi,

Sometimes I find it handy to construct an sql string for action queries, and
then run it directly against the database and/or connection instead of using
Recordsets for simple updates.
eg. This query works in MS Access (using dbs.Execute):

UPDATE Customer SET LastContact = #2/5/2005 13:15:00#
WHERE Customer.CustomerID = 101;

I'm now upsizing an app to SQL server, using ADP front-end.
Date/Time fields were converted (by the wizard) to MSSQL datetime fields.

I'm finding that Date/Time literal expressions (formatted as #m/d/yyyy
hh:nn:ss#) are not acceptible to MSSQL? Running above using cnn.Execute
gives SQL syntax error at the # symbol.

If so, how do you write a literal date in an SQL query for executing against
SQL server?

Thanks,
Ian.

[Tom van Stiphout]

On Sat, 15 Oct 2005 03:18:58 GMT, "Ian Hinson"
<pp******@bigpond.net.au> wrote:

In T-SQL, dates are wrapped in single quotes.
-Tom.

Hi,

Sometimes I find it handy to construct an sql string for action queries, and
then run it directly against the database and/or connection instead of using
Recordsets for simple updates.
eg. This query works in MS Access (using dbs.Execute):

UPDATE Customer SET LastContact = #2/5/2005 13:15:00#
WHERE Customer.CustomerID = 101;

I'm now upsizing an app to SQL server, using ADP front-end.
Date/Time fields were converted (by the wizard) to MSSQL datetime fields.

I'm finding that Date/Time literal expressions (formatted as #m/d/yyyy
hh:nn:ss#) are not acceptible to MSSQL? Running above using cnn.Execute
gives SQL syntax error at the # symbol.

If so, how do you write a literal date in an SQL query for executing against
SQL server?

Thanks,
Ian.

[Terry Kreft]

Use the Convert function against the string representation. e.g. with the
date as yyyy-mm-dd hh:mm:ss

CONVERT(datetime, '2005-02-05 13:15:00', 120)
Look in BOL for the CONVERT function for the list of formats converted.

--
Terry Kreft

"Ian Hinson" <pp******@bigpond.net.au> wrote in message
news:Cy******************@news-server.bigpond.net.au...

Hi,

Sometimes I find it handy to construct an sql string for action queries,
and then run it directly against the database and/or connection instead of
using Recordsets for simple updates.
eg. This query works in MS Access (using dbs.Execute):

UPDATE Customer SET LastContact = #2/5/2005 13:15:00#
WHERE Customer.CustomerID = 101;

I'm now upsizing an app to SQL server, using ADP front-end.
Date/Time fields were converted (by the wizard) to MSSQL datetime fields.

I'm finding that Date/Time literal expressions (formatted as #m/d/yyyy
hh:nn:ss#) are not acceptible to MSSQL? Running above using cnn.Execute
gives SQL syntax error at the # symbol.

If so, how do you write a literal date in an SQL query for executing
against SQL server?

Thanks,
Ian.