CODEWITHSUNDEEP
sqlsql-server-2008
omkar patade
omkar patade

Reputation: 1591

Update only time from my Datetime field in sql

I have a date 2013-12-14 05:00:00.000 in my table.

Now i want to update only time of that date. E.G to 2013-12-14 04:00:00.000

Is there any query to update only time from datetime field?

Upvotes: 41

Views: 115807

Answers (9)

Sirar Salih
Sirar Salih

Reputation: 2544

Use this to change the whole time part of the string:

UPDATE MyTable SET MyColumn = DATEADD(DAY, DATEDIFF(DAY, 0, MyColumn), '04:00:00')

Upvotes: 0

Nabster
Nabster

Reputation: 1227

In case of just updating a particular part of the datetime you can use SMALLDATETIMEFROMPARTS like:

UPDATE MyTable 
SET MyDate = SMALLDATETIMEFROMPARTS(YEAR(MyDate), MONTH(MyDate), DAY(MyDate), <HoursValue>, <MinutesValue>)

In other cases it may be required to copy parts of datetime to other or update only certain parts of the datetime:

UPDATE MyTable 
SET MyDate = SMALLDATETIMEFROMPARTS(YEAR(MyDate), MONTH(MyDate), DAY(MyDate), DATEPART(hour, MyDate), DATEPART(minute, MyDate))

Refer SQL Server Date/Time related API references for more such functions

Upvotes: 3

Abolfazl
Abolfazl

Reputation: 11

use this script:

declare @curDate datetime=getdate() --or any other datetime 
declare @time time='22:31'
declare @hour int ,@min int
set @hour=datepart(hh,@time)
set @min=datepart(mm,@time)
select @curDate=Dateadd(HOUR,@hour,convert(datetime,convert(Date,@curDate)))
select @curDate=Dateadd(MINUTE,@min,@curDate)
Select @curDate

Upvotes: 0

Jahangir Alam
Jahangir Alam

Reputation: 23

The previous query update only the hour portion but this query will update all portions of the time i.e hour, minutes, seconds:

UPDATE TABLE_NAME
SET DATETIME_FIELD = CONCAT(CAST(DATETIME_FIELD AS Date), ' ', CAST('2016-02-01 09:20:00.0000000' AS TIME))

Upvotes: -2

Sumanth
Sumanth

Reputation: 121

User the below one to update the Time only for datetime column - 

select  CONVERT(varchar(10),datecolumn, 120) + ' 12:34:56', 
        CONVERT(varchar(10),datecolumn, 120) + ' 00:00:00', 
        RSLV_LTR_INITIAL_DUE_DT, *
from twhere able1

Upvotes: 12

Amir Alizade
Amir Alizade

Reputation: 1

DECLARE @d datetime;
SELECT @d = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);

DECLARE @t time(7);
SET @t = CAST('10:10:10' AS time);

SELECT CONVERT(datetime, CONVERT(varchar(10), CONVERT(date, @d, 101)) + ' ' + CONVERT(varchar(8), @t));

Upvotes: 0

Pranesh Janarthanan
Pranesh Janarthanan

Reputation: 1194

DECLARE @Time as TIME;
DECLARE @Date as DATETIME;

SELECT @Time = CONVERT(TIME, '2016-01-01 09:30:00.000');
SELECT @Date = CONVERT(date, GETDATE()); --2017-03-10 16:37:34.403

SELECT DATEADD(MINUTE, DATEPART(MINUTE, @Time), DATEADD(HH, DATEPART(HOUR, @Time), @Date))

OUTPUT: 2017-03-10 09:30:00.000

Upvotes: 5

IUnknown
IUnknown

Reputation: 22478

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))

Upvotes: 59

Samina
Samina

Reputation: 154

UPDATE TABLE_NAME SET DATETIME_FIELD = CAST(CAST(CONVERT(DATE, DATETIME_FIELD,101) AS VARCHAR) + ' 2' + ':' +  '22' AS DATETIME) WHERE (OUR_CONDTTION)

Upvotes: 3

Related Questions