Update Only Time from My Datetime Field in SQL

Update only time from my Datetime field in sql

UPDATE MyTable 
SET MyDate = DATEADD(HOUR, 4, CAST(CAST(MyDate AS DATE) AS DATETIME)) 

Or this

UPDATE MyTable 
SET MyDate = DATEADD(HOUR, 4, CAST(FLOOR(CAST(MyDate AS FLOAT)) AS DATETIME))

Update only time in a SQL Server DateTime column from another DateTime column in the same table

Adding the time from a to b after truncating the time portion of b:

select 
    a
  , b = dateadd(second,datediff(second,convert(date,a),a),convert(datetime,convert(date,b)))
from t

rextester demo: http://rextester.com/IRTP42230

returns:

+---------------------+---------------------+
|          a          |          b          |
+---------------------+---------------------+
| 2017-07-07 16:30:00 | 2017-08-30 16:30:00 |
+---------------------+---------------------+

How to update only the hour from a DATETIME field in MySQL?

SQL

UPDATE datetimes
SET datetime = DATE_ADD(datetime,
                        INTERVAL (15 - HOUR(datetime)) HOUR);

Demo

http://rextester.com/JOJWJ94999

Explanation

DATE_ADD(datetime, INTERVALintervalHOUR) adds or subtracts interval hours from datetime (depending on whether interval is positive or negative). The number of hours to add or subtract is calculated by subtracting the number of hours part of datetime (found from HOUR(datetime)) from 15. If the current time is 16:00 or after, this will be negative and if the current time is before 15:00, it will be a positive number. There is no WHERE clause so all rows in the table will be updated.

Update only time in timestamp postgresql

update p_bab.registro_ponto
set data_hora = data_hora - interval '1 hour'
where id = 50;

if not and you have a constant hour to set then :

update p_bab.registro_ponto
set data_hora = date(data_hora) + interval '10 hour'
where id = 50;

or

update p_bab.registro_ponto
set data_hora = date(data_hora) + '10:30:23'::time
where id = 50;

Update datetime fields date only

I believe you want to only update DateTimeField1 & DateTimeField2 when they are less than '20000101'. CASE Statement will take care of not updating wrong field.

Try single query UPDATE -

SQL SERVER 2008 AND LATER -

UPDATE  Table1

SET DateTimeField1 = (CASE WHEN (DateTimeField1 < '20000101') 
                      THEN CAST(CAST (DateTimeField3 AS DATE) AS DATETIME)
                      + CAST (DateTimeField1 AS TIME)
                      ELSE DateTimeField1
                  END)
, DateTimeField2 = (CASE WHEN (DateTimeField2 < '20000101') 
                      THEN CAST(CAST (DateTimeField3 AS DATE) AS DATETIME)
                      + CAST (DateTimeField2 AS TIME)
                      ELSE DateTimeField2
                  END)
WHERE (DateTimeField1 < '20000101') OR (DateTimeField2 < '20000101');

EARLIER THAN SQL SERVER 2008 -

UPDATE Table1 

SET DateTimeField1 = (CASE WHEN (DateTimeField1 < '20000101') 
                      THEN DATEADD(DAY, 0, DATEDIFF(day, 0, DateTimeField3)) 
                      + DATEADD(DAY, 0 - DATEDIFF(day, 0, DateTimeField1), DateTimeField1)
                      ELSE DateTimeField1
                 END)
, DateTimeField2 = (CASE WHEN (DateTimeField2 < '20000101') 
                      THEN DATEADD(DAY, 0, DATEDIFF(day, 0, DateTimeField3)) 
                      + DATEADD(DAY, 0 - DATEDIFF(day, 0, DateTimeField2), DateTimeField2)
                      ELSE DateTimeField2
                 END)
WHERE (DateTimeField1 < '20000101') OR (DateTimeField2 < '20000101');

---

Related Topics