|
Subject:
|
ASP, Sql Server DateTime field
|
|
Posted By:
|
itHighway
|
Post Date:
|
8/14/2005 8:53:54 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
|
|
Reply By:
|
mat41
|
Reply Date:
|
8/14/2005 6:32:17 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
|
