Datetime error 80040e07 on Update statement (DWUD)

I have a problem with updating a field which hold a datetime. When I use <%=NOW%> for inserting a datetime into my MS SQL database everything goes well....When I try to update this by using <%=NOW%> it returns the following error...

Code:

---

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to
a datetime data type resulted in an out-of-range datetime value.

---

I have been trying to convert and format the time in many many different ways for a few days now....does anyone know how to solve this problem?

What are the regional settings for the SQL database?

'16/1/03' in American format (mm/dd/yy) would be out of range, but not in British/English (dd/mm/yy)...


--
Anglo Saxon

datetime shows up as '17-1-2003 8:44:48' in the DB

strange thing is when inserting it works but updating it won't...

and what does the date value show up as when you do a response.write Now() ??

The other thing you could do is set the field to a default value of getdate() , you may still have problems on seraches which had dates as the conditional statements though....

--
Anglo Saxon

Code:

---

update dbo.Bla set IP = '156.345.1.65,ChangedBy = '5',LastChange = '1/17/2003 10:24:33 AM',SystemStatus = 'not active' where CallID = 40

---

The query shows up like above when debug printing it to the browser

I've been playing around with SQL Analyser....it accepts a date Formatted as '17-1-2003 10:24:33 AM'

so I think my question changes a little...how to format the date to get the above date...

Originally posted by Chrissie
update dbo.Bla set IP = '156.345.1.65,ChangedBy = '5',LastChange = '1/17/2003 10:24:33 AM',SystemStatus = 'not active' where CallID = 40


Notice anything about the date??

--
Anglo Saxon

Yeah just noticed it..see my above post..

This should give you some ideas :

http://www.aspfaq.com/show.asp?id=2260

Happy reading! ;)

--
Anglo Saxon

Thanks got it working now...really strange sometimes it gives the error...sometimes not...