473,835 Members | 2,159 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 6197
-----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(),"y yyy-mm-dd hh:mm:ss") & "' " & _
"where openid= '" & tmpopen & "'"

HTH,

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

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

iQA/AwUBQAbQXoechKq OuFEgEQJU1ACgq3 0g43zKxdqEZ1FX8 KfZ3kEjPGoAnRKQ
+95a9jGvRma++yt Y5fffsl3h
=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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
4902
by: Harag | last post by:
Hi all I think this is in the wrong group but since I don't read others much or code in java script I was wondering if anyone could help me with this small problem, as I code mostly in ASP vbscript. I have the following piece of Javascript client code that displays the users clock on the browser and updates it every second.
18
18454
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on a remote update of tables and fields and can't find enough information on these things. Also, how do you index a field in code?
4
2037
by: Jeff Cope | last post by:
I'd like to have a text box on my webform and a button beside that will set the current date in the text box when the button is clicked. Is there a way to do this without doing a postback? Thanks for your help. Jeff
1
1375
by: Mike | last post by:
Hi, I am developing a JavaScript calendar control. I want my user control to expose a property that returns the value of the selected calendar date. I have an "input" control that displays the selected date. I also tried to add a Label server-side control, but I am unable to set its value through Javascript. <form id="mainForm" name="mainForm" action="" method="post"> <p>
3
1956
by: Patrick | last post by:
I am dynamically creating TextArea and drop-down lists in ASP.NET using something like HtmlTextArea eachTextArea = new HtmlTextArea(); I tried to set the "name" of these TextAreas, etc. (e.g. <textarea name="NameOfTextArea" rows="5" cols="60">some text area</textarea>), as I want to then dynamically read back the controls' values Setting the ID attributes doesn't work, nor does the following:...
8
2270
by: David Lozzi | last post by:
Howdy, I have a user control that is a report to display data. On the page the control is inserted in, I have filter options to filter the report. When I try to do something like this, nothing happens. dim filt as string ... build filter string... UserControl.ReportFilter = filt
5
3175
by: Amogh | last post by:
Hi, My question is related to setting freed pointers to NULL. After freeing a pointer: 1) Should the freeing routine also be responsible for setting the pointer to null? 2) Or, should the client/user code be responsible for doing it? On what basis should a decision be made favouring either case ?
1
6518
by: laredotornado | last post by:
Hi, I'm using PHP 4.4.4 on Apache 2 on Fedora Core 5. PHP was installed using Apache's apxs and the php library was installed to /usr/local/php. However, when I set my "error_reporting" setting to be "E_ALL", notices are still not getting reported. The perms on my file are 664, with owner root and group root. The php.ini file is located at /usr/local/lib/php/php.ini. Any ideas why the setting does not seem to be having an effect? ...
1
2237
by: sidathkp | last post by:
Here is a sample of the xml file that I have: <provider> <cinames> <cinema> <id>1111</id> <name>ABC cinema</name> <address>123 street</address> </cinema> <cinema>
0
9803
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10811
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10523
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10233
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9345
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5636
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5804
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4434
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.