-
Jan 20th, 2020, 08:40 AM
#1
Thread Starter
Fanatic Member
Subtract date and time fields in SQL Server query
I have four fields in table1 in Sql Server 2012.
Two are of type date (storing date1 and date2) and two are of type datetime (storing time1 and time2).
How do I calculate the difference in hours between date1 + time1 and date2 + time2 in a query?
Thanks
PK
-
Jan 20th, 2020, 09:59 AM
#2
Re: Subtract date and time fields in SQL Server query
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jan 21st, 2020, 05:31 AM
#3
Re: Subtract date and time fields in SQL Server query
As a general tip, there's no need to separate those fields. A DateTime is sufficient to hold both a Date and a Time. If you'd done that it would be a simple matter of using the DateDiff function Sapator has linked to.
As you've got the time in it's own field things will get a little more sketchy as we don't really know what's in the Date part of the DateTime field - if it's holding actual Dates then how does that interact with the Date Fields if they differ?
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Jan 21st, 2020, 12:50 PM
#4
Re: Subtract date and time fields in SQL Server query
Code:
declare @my_date datetime
, @date_only date
, @time_only datetime
set @my_date = getdate()
SELECT @my_date AS [Original date & time]
-- get date only, get time only
SELECT @date_only = @my_date, @time_only = cast(@my_date as float) - floor(cast(@my_date as float))
-- display the values
SELECT @date_only as [Date only], @time_only as [Time only]
-- convert date to datetime, then to float
-- you cannot convert date to float, but datetime CAN be converted to float
-- add the 2 float numbers, then convert back to datetime
select cast(cast(cast(@date_only as datetime) as float) + cast(@time_only as float) as datetime) AS [date & time combined]
Once you combine the date & time into one, then you can just use DATEDIFF(hour, start_date, end_date)
-
Jan 22nd, 2020, 11:13 AM
#5
Re: Subtract date and time fields in SQL Server query
The worry is that this:-
Code:
@time_only = cast(@my_date as float) - floor(cast(@my_date as float))
...assumes that the content of the Date part of the DateTime field holds no significance.
If that's true then your suggestion is basically what I'd suggest (I'd suggest casting it to a Time because it's more explicit but it wouldn't surprise me if the float operation you're is actually more performant). If the date part of the DateTime field is significant then we'd need to understand what it contains.
Basically, since the introduction of the Date and Time types there's really no need to ever use part of a DateTime field. You either use a DateTime or a Date and a Time, depending on you predicted access pattern.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Jan 22nd, 2020, 01:35 PM
#6
Re: Subtract date and time fields in SQL Server query
Lets not forget DATETIMEOFFSET... I have to use this since we are across time zones. We always store UTC datetime and then use AT TIMEZONE 'XXXX' to display the local time
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Jan 23rd, 2020, 07:03 AM
#7
Re: Subtract date and time fields in SQL Server query
I wasn't aware of the DateTimeOffset so thanks for bringing it up. I tried to Rep you but couldn't so you'll have to settle for a hearty "Thanks".
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Jan 23rd, 2020, 10:41 AM
#8
-
Jan 23rd, 2020, 11:43 AM
#9
Thread Starter
Fanatic Member
Re: Subtract date and time fields in SQL Server query
GaryMazzone and FunkyDexter,
Thanks for your valuable contributions.
I have tried that of FunkyDexter and it works excellent.
PK
-
Feb 7th, 2020, 12:09 AM
#10
Lively Member
Re: Subtract date and time fields in SQL Server query
Hello, @Peekay
To Subtract date and time fields in SQL Server then follow below query.
Code:
DECLARE @StartTime DATETIME = '2011-09-23 15:00:00',
@EndTime DATETIME = '2011-09-23 17:54:02'
SELECT CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND,@StartTime, @EndTime),0), 108) as ElapsedTime
I hope above query will be useful for you.
Thank you.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|