SQL update date/time

[0xC0000005]

How do I update a date/time field in a Microsoft Access database using SQL Execute on an Active Server Page?

I have tried several variations of this statement but none work. They all give me a generic "Error in UPDATE statement" message.

strProvider = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:/Inetpub/wwwroot/DBDIR/mydb.mdb;"
dbObj = Server.CreateObject("ADODB.Connection");
dbObj.Open(strProvider);

d = new Date;
sqlUpdate = "UPDATE Connections SET time='" + d + "' WHERE name='somebody'";
dbObj.Execute(sqlUpdate);

[davide++]

Hi all.

There're several ways.
For example


DIM strDate AS String

strDate = "01/10/2008" 'European format, but don't worry

sqlUpdate = "UPDATE Connections SET time= CVDate(" + strDate + ") WHERE name='somebody'";
dbObj.Execute(sqlUpdate);


Here I'm using the (old) CVDate function, that converts string to date.
Another way is using date literals


DIM strDate AS String

strDate = "#01/10/2008#"

sqlUpdate = "UPDATE Connections SET time= " + strDate + " WHERE name='somebody'";
dbObj.Execute(sqlUpdate);


I hope this will help you

[0xC0000005]

Thanks for the help, but I still must be doing something wrong. Here are the actual strings - as if they were hard-coded:

"UPDATE Connections SET time= CVDate(10/01/2008) WHERE name='dean'"
"UPDATE Connections SET time= #01/10/2008# WHERE name='dean'"

I did a search for date literals in SQL and found another format, but it doesn't work either.

"UPDATE Connections SET time= {d '2008-10-01'} WHERE name='dean'"

It's got to be the time update that is causing a problem because if I change the time column to some other field it works. For example:

"UPDATE Connections SET ip= '192.168.1.100' WHERE name='dean'"

[mmetzger]

Try wrapping time in brackets - I haven't played with Access in a while, time may be a reserved word.

[0xC0000005]

Try wrapping time in brackets - I haven't played with Access in a while, time may be a reserved word.I thought of that too and actually changed the column name - but it still doesn't work.

If I change the column (field) type to "Text" rather than "Date/Time" it works. It has something to do with the Access "Date/Time" data type.

For now, I am going to stick with text to store date and time unless someone can suggest another solution.

[mmetzger]

What version of Access are you using? I just tried this type of query and it worked (at least in the Access SQL view) - I remember doing this but I can't place what was different / strange (other than + not being used for string concatenation, only &).

[davide++]

Hi all.

Please don't change the data type!!!!
Sooner or later you'll regret!!!!

Try this


"UPDATE Connections SET time= CVDate('10/01/2008') WHERE name='dean'"
"UPDATE Connections SET time= '#01/10/2008#' WHERE name='dean'"


In both cases the date is put as string, so it must be delimited by '.

I hope this works at last