ASP, Sql Server DateTime field

itHighway · August 14th, 2005, 08:53 AM

Hello,

I am having problem with DateTime field in Sql Server db.

FieldName: LastActionBy
FieldType: DateTime

I want to Update Current Date and Time and using following query.

"Update LOGS SET LastActionBy = '"& NOW() &"' WHERE ID = 5"

The error I get is
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"

The above mentioned query was working fine. But now it is not.

I tried following queries as well, but getting same result.
"Update LOGS SET LastActionBy = "& NOW() &" WHERE ID = 5"
"Update LOGS SET LastActionBy = #"& NOW() &"# WHERE ID = 5"

Please help!!

Regards
Zeeshan Ahmed

mat41 · August 14th, 2005, 06:32 PM

There is nothing wrong with your first query. The error leads me to believe your date format is incorrect. write your query to the browser, what format is the date: dd/mm/yyyy, mm/dd/yyyy, yyyy/dd/mm

Im my experience you should be using either mm/dd/yyyy, yyyy/dd/mm (standard) for an insert. Here in Aussie we use dd/mm/yyyy, this is the format I get dates from users, I run the following function to insert in american date format:

  FUNCTION amDate(varDate)
    IF isNull(varDate) OR Trim(varDate) = "" OR varDate = "Null" THEN
       amDate = "Null"
    ELSE
       amDate = "'" & Month(DateValue(varDate)) & "/" & Day(DateValue(varDate)) & "/" & Year(DateValue(varDate)) & " " & TimeValue(varDate) & "'"
    END IF
  END FUNCTION

Try this (single quotes not neccessary, the function puts these in there, its a good practice to always use triling semi colons):
"Update LOGS SET LastActionBy = " & amDate(NOW()) & " WHERE ID = 5;"

NOTE: This is what query analyser is for. Trial and error, test n run, change test n run ....

Wind is your friend
Matt