February 8, 2007 at 8:16 am
I am using MS SQL Server 2000.
I have 4 text fields
1. event begin date
2. event begin time
3. event end date
4. event end time
I have to find the day and time difference in months days hours and minutes between the event begin date & time and the event end date & time – The event begin date and event end date are char(8) & event begin time and event end time are char(4) – with military time. I would need to combine the event begin date & event begin time to get the event begin date & time and the same for the event end date & time.
If the values of the 4 text fields are
event begin date = ‘20070207’, event begin time = ‘2015’, event end date = ‘20070208’, event end time = ‘0105’, the difference should be 0 month 0 day 4 hours 50 minutes.
Can somebody please help with MS SQL Server 2000 syntax?
February 8, 2007 at 8:52 am
This is a bit long-winded and there may be a better way (you could also make this into a function) but:
DECLARE @beginDate VARCHAR(8)
DECLARE @beginTime VARCHAR(8)
DECLARE @finalDate VARCHAR(8)
DECLARE @finalTime VARCHAR(8)
SET @beginDate = '20070207'
SET @beginTime = '0000'
SET @finalDate = '20070208'
SET @finalTime = '0705'
DECLARE @beginDateTime DATETIME
DECLARE @finalDateTime DATETIME
DECLARE @minDiff INT
DECLARE @result VARCHAR(100)
SET @beginTime = LEFT(@beginTime,2) + ':' + SUBSTRING(@beginTime,3,2) + ':' + '00'
SET @finalTime = LEFT(@finalTime,2) + ':' + SUBSTRING(@finalTime,3,2) + ':' + '00'
SET @beginDateTime = CONVERT(datetime, @beginDate, 112) + CONVERT(datetime, @beginTime, 108)
SET @finalDateTime = CONVERT(datetime, @finalDate, 112) + CONVERT(datetime, @finalTime, 108)
SELECT @minDiff = DATEDIFF(mi, @beginDateTime, @finalDateTime)
SET @result = ''
IF @minDiff > (60*24)
BEGIN
SET @result = @result + CAST((@minDiff / 60/ 24) AS VARCHAR) + ' Days, '
SET @minDiff = @minDiff - (ROUND((@minDiff /60/24), 0, 1) * 60 * 24)
END
ELSE
SET @result = @result + '0 Days, '
IF @minDiff > 60
BEGIN
SET @result = @result + CAST((@minDiff / 60) AS VARCHAR) + ' Hours, '
SET @minDiff = @minDiff - (ROUND((@minDiff / 60), 0, 1) * 60 )
END
ELSE
SET @result = @result + '0 Hours, '
IF @minDiff > 0
BEGIN
SET @result = @result + CAST(ISNULL(@minDiff,0) AS VARCHAR) + ' Minutes'
SET @minDiff = @minDiff - ROUND(@minDiff, 0, 1)
END
SELECT @result
February 8, 2007 at 9:18 am
If possible, change the dates and times to use datetime.
Also, how are you going to define a month? For days, hours and minutes the following should work:
SELECT D.MiDiff/1440 AS Days
,D.MiDiff%1440/60 AS Hours
,D.MiDiff%1440%60 AS Minutes
FROM ( SELECT DATEDIFF(mi, BeginDate + ' ' + LEFT(BeginTime, 2)
+ ':' + RIGHT(BeginTime, 2)
,EndDate + ' ' + LEFT(EndTime, 2)
+ ':' + RIGHT(EndTime, 2))
FROM YourTable) D (MiDiff)
February 9, 2007 at 7:38 am
Here's Ken's version, which works quite well, using Stuff() instead of Left() and Right():
SELECT D.MiDiff/1440 AS Days
,D.MiDiff%1440/60 AS Hours
,D.MiDiff%1440%60 AS Minutes
FROM ( SELECT DATEDIFF(mi, BeginDate + ' ' + Stuff(BeginTime,3,0,':')
, EndDate + ' ' + Stuff(EndTime,3,0,':'))
FROM YourTable) D (MiDiff)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply