CODEWITHSUNDEEP
sqlsql-serverdatetimesql-update
Mason Chambers
Mason Chambers

Reputation: 173

SQL query executes successfully but no change is made

I have a table with a DateTime column Move_Date. The dates are correct, but the time portion is zeroed out so I tried to replace the zeroed columns with the correct time. my query is as follows:

update TableA 
set Move_Date = replace(Move_Date, '00:00:00.000', '18:00:00.000')

When I run this query it succeeds with 1,632,380 rows affected, but no change has been made in the table at all. Is there something wrong with my query or is this a bug?

Upvotes: 2

Views: 550

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272296

Assuming you want to set the time portion:

UPDATE table1
SET Move_Date = CAST(CAST(Move_Date AS DATE) AS DATETIME) + CAST('18:00' AS DATETIME)
-- WHERE CAST(Move_Date AS TIME) =  '00:00'
-- WHERE CAST(Move_Date AS TIME) <> '18:00'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

For a datetime column, don't use string functions! I think the simplest is dateadd():

update TableA
    set Move_Date = dateadd(hour, 18, move_date);

If you only want to do this when the date is already midnight, then add a proper filter:

update TableA
    set Move_Date = dateadd(hour, 18, move_date)
    where move_date = convert(date, move_date);

Upvotes: 4

Related Questions