Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

Update errors on a SmallDateTime field

I am trying to update a table and the DateInactive smalldatetime field is causing errors. Here are the statements and errors.

UPDATE Categories SET DateInactive = '7/31/2005 15:32', AdminInactivating = 36 WHERE CategoryId = 15
causes
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '7'.

UPDATE Categories SET DateInactive = '7/31/2005', AdminInactivating = 36 WHERE CategoryId = 15
causes
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '7'.

UPDATE Categories SET DateInactive = 'CONVERT(datetime, '7/31/2005 15:42', 131)', AdminInactivating = 36 WHERE CategoryId = 15
causes
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'CONVERT'.

UPDATE Categories SET DateInactive = CONVERT(datetime, '7/31/2005 15:43', 131), AdminInactivating = 36 WHERE CategoryId = 15
causes
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '7'.

UPDATE Categories SET DateInactive = 7/31/2005, AdminInactivating = 36 WHERE CategoryId = 15
WORKS!
but the date in the database is 1/1/1900

UPDATE Categories SET DateInactive = CONVERT(smalldatetime, '7/31/2005 15:46', 131), AdminInactivating = 36 WHERE CategoryId = 15
causes
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '7'.

Can you give me a solution as to how to update this field?
Thanks,
-
mickers

[1990 byte] By [mickers] at [2008-2-21]
«« Detecting remote control device events on a browser
»» Binding to custom collection - select columns to display
# 1

can you specify which version of SQL server you use ?
Eisa at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2
Sorry, SQL Server 2000.
mickers at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3
Any ideas? I am sure this shouldn't be too bad, but I'm stuck.
Thanks,
mickers at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 4
The UPDATE statement itself looks fine although some of the values may not convert properly depending on your date format or language setting for example. And you would typically get a character value conversion error. But I think the problem is in your application code that is not setting the value properly. What is the client-side API? How are you setting the value of the date column? SInce this is syntax error, there is something wrong when the statement reaches the server. Can you check the Profiler output since I am not sure where you got these statements from?
UmachandarJayachandran-MS at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 5
Can you show me some example insert or update sql syntax that would work for any smalldatetime field?
Thanks.
mickers at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 6
Are you running this query from ms access? If so, try using # around the dt value. '#7/31/05 15:31#'. This will tell odbc to convert the dt value to an odbc timestamp that the server will recognize.
wkb at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 7
I am running this in ColdFusion, but I just need a valid SQL statement that will be able to insert or update a smalldatetime field.
mickers at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 8

There is no difference in the query that updates a smalldatetime field as a datetime field; both statements are the same, you just need to stay within the smalldatetime range which of course you're probably doing.

Any of the queries you mention above should all work in native SQL except for the one without the quotes; this is why we're asking what you're api is etc.

Given this, it has to be the way that you're stucturing your <cfquery> or <cfupdate> so this is what you need to be investigating. I assume you're updating the database with a coldfusion variable; is it nested in pounds and single quotes? '#variable_sdt#'. If you're not nesting it correctly, you will get this type of error when it reaches the server.

wkb at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 9
Hi,
The Profiler suggestion did it! The statement getting into the SQL Server was:
UPDATE Categories SET DateInactive = ''8/8/2005'', AdminInactivating = 36 WHERE CategoryId = 10

I used the PreserveSingleQuotes() function within the query and it stripped out the second set of apostrophes.

Thanks for the help. This has been killing me, and now I can move on.

Best regards.

mickers at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified