Correct datetime syntax for MSSQL?

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.

re: Correct datetime syntax for MSSQL?

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

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

[color=blue]
>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.
>[/color]

re: Correct datetime syntax for MSSQL?

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" <pparagon@bigpond.net.au> wrote in message
news:Cy_3f.18178$U51.8689@news-server.bigpond.net.au...[color=blue]
> 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.
>
>[/color]