T-SQL How to update hour in a SmallDateTime column?

let's say that I have a SmallDateTime column in my table. How to upda开发者_如何学Pythonte hours in each row in T-SQL ?

1. Get the target value's hour part.
2. Find the difference between the hour you want and the found hour.
3. Add the difference of hours to the target value.

The script:

UPDATE atable
SET datetimevalue = DATEADD(hour, @hour - DATEPART(hour, datetimevalue),
                                  datetimevalue)
WHERE ...

UPDATE YourTable SET YourDateColumn = DateAdd(hh, 1, YourDateColumn)

will add 1 hour to the time value in YourDateColumn in every single row.

See this page for more info.

UPDATE YourTableName 
SET YourSmallDateTimeColumn = DATEADD(HH, 1, YourSmallDateTimeColumn)

Will add any number of hours to your column. So if you get rid of the time component first:

SELECT CAST(CONVERT(char(8), YourSmallDateTimeColumn, 112) AS smalldatetime) 

and add hour component to it later it should work.

From here

To add or subtract hours to a datetime or smalldatetime value, you will use the DATEADD date function. The DATEADD date function returns a new datetime value based on adding an interval to the specified date. The syntax of the DATEADD date function is as follows:

DATEADD ( datepart , number, date )

datepart is the parameter that specifies on which part of the date to return a new value. For hours, you can use either HOUR or HH. number is the value used to increment datepart. date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Here's an example on how to use the DATEADD date function to increase or decrease a datetime value by a certain number of hours:

SELECT DATEADD(HOUR, -12, GETDATE())AS [TwelveHoursAgo]
SELECT DATEADD(HH, 6, GETDATE()) AS [SixHoursLater]