469,903 Members | 1,511 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

setting a date value to sql server

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
1 6044
-----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.

Similar topics

7 posts views Thread by Harag | last post: by
4 posts views Thread by Jeff Cope | last post: by
1 post views Thread by Mike | last post: by
3 posts views Thread by Patrick | last post: by
8 posts views Thread by David Lozzi | last post: by
5 posts views Thread by Amogh | last post: by
1 post views Thread by sidathkp | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.