SSMS - copy column values into another column within the same table

Question

I am using SSMS, I want to copy 'created_datetime' column's values into another column within the same table. But There are two conditions

1] if the created_datetime column's value (datetime data type) is between 12:00am to 9:00am then we have to make it as 9:00am along with date.

Ex- 2023-03-10 01:05:45 then we have to make it 2023-03-10 9:00:00 or 9:00 into another column

2] if the column's value is 2023-03-10 13:05:45 then we have to copy as it 2023-03-10 13:05:45

Note- Consider time between 12:00:00 to 9:00:59 then 9am.

If not then copy as it.

Answer 1

Hi @Cool Sam

Notice that your description of copy logic is slightly inconsistent.

between 12:00am to 9:00am then we have to make it as 9:00am along with date. Note- Consider time between 12:00:00 to 9:00:59 then 9am.

If the second is a typo, then DATEPART will be fine.

If not, then DATEPART should not be used here.

Please try this solution:

--Add new column to your table
ALTER TABLE yourtable ADD new_column DATETIME;

--Copy values to this new_column using UPDATE
UPDATE yourtable
SET new_column = CASE WHEN CAST(created_datetime AS TIME)>'9:00:59'
                      THEN created_datetime                      
					  ELSE DATEADD(hour,9,CAST(CAST(created_datetime AS DATE)AS DATETIME))
                 END

Also, you could update twice with different where conditions.

UPDATE yourtable
SET new_column = created_datetime
WHERE CAST(created_datetime AS TIME) > '9:00:59'

UPDATE yourtable
SET new_column = DATEADD(hour,9,CAST(CAST(created_datetime AS DATE)AS DATETIME))
WHERE CAST(created_datetime AS TIME) < '9:00:59'

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can perform an UPDATE statement using CASE against the DATEPART to determine what the new column's datetime should be.

Answer 3

UPDATE tbl
SET othercol = CASE WHEN datepart(HOUR, created_datetime) < 9 
                    THEN dateadd(HOUR, 9, convert(datetime, convert(date, created_datetime)))
                    ELSE created_datetime
               END