How do I update a record where time > '13:00:00.0'

Question

UPDATE TimeRecord 
SET activityid = 151
WHERE timevalue >= '13:00:00.0'

When I run the query it updates every record instead of the ones greater than the specified timevalue

The timevalue column is formatted as follows

2012-05-24 13:00:00.0

Do you want all dates where time >13 or just current date? Also, see this answer: http://stackoverflow.com/a/7710495/1618257 — David Starkey, May 24 '13 at 20:50
@a_horse_with_no_name based on syntax it's `not IN ('oracle', 'postgres', 'ms-access')` could be `in ('MySQL', 'Sql Server' )` not sure about others — Conrad Frix, May 24 '13 at 20:57

score 1 · Accepted Answer · answered May 24 '13 at 20:48

1

Without a date specified, you default to January, 01 1900 13:00:00, so you're asking to update any value where timevalue is after that date, which is quite likely all of them.

You probably want to use:

UPDATE TimeRecord 
SET activityid = 151
WHERE DATEPART(HOUR, timevalue) > 13;

answered May 24 '13 at 20:48

Adam Wenger

17,100
6
52
63

1

Likewise, WHERE CAST(timevalue AS TIME) >= '13:00:00.0' – Hart CO May 24 '13 at 20:59

How do I update a record where time > '13:00:00.0'

1 Answers1