By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,501 Members | 831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,501 IT Pros & Developers. It's quick & easy.

setting a date value to sql server

P: n/a
I'm trying to set the current time using now() to a date field on a table
which is in sql server.
I've tried the following syntax:

DoCmd.RunSQL "update openclosepos set closedate= '" & now() & "' where
openid= '" &
tmpopen & "'"

but then I get an error which says that it cannot enter a string value into
a date field.

I've tried:

DoCmd.RunSQL "update openclosepos set closedate= #" & now() & "# where
openid= '" & tmpopen & "'"

but then I get an error which says that there is a syntax problem near the
#.

What is the right syntax ? Please keep in mind that closedate is defined as
a date field.

Thanks!
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Now() returns the date and time in this format:

1/15/2004 9:28:26 AM

SQL Server likes its date/time data formatted like this:

'2004-01-15 09:28:26'

If the time were 9 PM the time would be in 24-hour format: 21:28:26.

So you can change your SQL string to this:

strSQL = "update openclosepos set closedate= '" & _
Format(now(),"yyyy-mm-dd hh:mm:ss") & "' " & _
"where openid= '" & tmpopen & "'"

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAbQXoechKqOuFEgEQJU1ACgq30g43zKxdqEZ1FX8KfZ3k EjPGoAnRKQ
+95a9jGvRma++ytY5fffsl3h
=aa7B
-----END PGP SIGNATURE-----
Marius Kaizerman wrote:
I'm trying to set the current time using now() to a date field on a table
which is in sql server.
I've tried the following syntax:

DoCmd.RunSQL "update openclosepos set closedate= '" & now() & "' where
openid= '" &
tmpopen & "'"

but then I get an error which says that it cannot enter a string value into
a date field.

I've tried:

DoCmd.RunSQL "update openclosepos set closedate= #" & now() & "# where
openid= '" & tmpopen & "'"

but then I get an error which says that there is a syntax problem near the
#.

What is the right syntax ? Please keep in mind that closedate is defined as
a date field.

Thanks!


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.