UPDATE fails...

[Quehay]

This fails:
[tt]
update reports
set page1_review_date = '11/14/2002',
recommendation_blood_pressure='bp rec',
recommendation_antiprotein='ap rec',
recommendation_protein_intake='bp rec'
where visit_guid='{F2482D81-95A4-11D6-987F-064514469200}'[/tt]

With this message:

Server: Msg 295, Level 16, State 3, Line 1
Syntax error converting character string to smalldatetime data type.

It updates when only the date field is attempted, e.g.:

[tt]update reports
set page1_review_date = '11/14/2002'
where visit_guid='{F2482D81-95A4-11D6-987F-064514469200}'[/tt]

This is RIDICULOUS! Any hints as to why SQL Server can't handle this standard UPDATE?

Thanks!

 

[MeanGreen]

Maybe because you have more than just the datetime field. MS Sql will usually convert datetimes for you, but not sure when you have other fields if the convert is getting confused or what. Try this:

update reports
set page1_review_date = convert(smalldatetime,'11/14/2002',101),
recommendation_blood_pressure='bp rec',
recommendation_antiprotein='ap rec',
recommendation_protein_intake='bp rec'
where visit_guid='{F2482D81-95A4-11D6-987F-064514469200}'

Hope this helps.