How to Split Date which straddles at midnight?

[rjohn2001]

Hi guys


How can i display a date which straddles at midnight, as two seperate dates

here is my data

id   start 		   end 			Duration
	
1    18/07/2005 18:00:00   19/07/2005 04:30:00	10.30
2    18/07/2005 09:00:00   18/07/2005 17:30:00	8.30

should be displayed as


id  start   		end 			Duration
	
1   18/07/2005 18:00:00	18/07/2005 00:00:00	6.0
1   19/07/2005 00:00:00	19/07/2005 04:30:00	4.30
2   18/07/2005 09:00:00	18/07/2005 17:30:00	8.30

Is this is possible using sql. Thanks for any help

 

[gmmastros]

>> Is this is possible using sql.

Yes.

I notice that your durations are not actually a duration in terms of hours. 17:30 - 9:00 should be 8.5 hours, but you are displaying it as 8.30. Is this an oversight or do you really want it displayed that way?

Also, I noticed in your expected results that you are showing your end as the same day as your start.

18/07/2005 18:00:00 18/07/2005 00:00:00

So, technically, the end occured before the start. Shouldn't this be...

18/07/2005 18:00:00 1[!]9[/!]/07/2005 00:00:00



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rjohn2001]

Thanks George, I am out of my mind. Here is the modified sample data.

id   start                  end                  Duration
    
1    18/07/2005 18:00:00   19/07/2005 04:30:00    10.5
2    18/07/2005 09:00:00   18/07/2005 17:30:00    8.5

should be displayed as


id  start                   end                  Duration
    
1   18/07/2005 18:00:00    18/07/2005 00:00:00    6.0
1   19/07/2005 00:00:00    19/07/2005 04:30:00    4.5
2   18/07/2005 09:00:00    18/07/2005 17:30:00    8.5

any clues how to do this..

 

[gmmastros]

Sorry. I got pulled away in to a meeting. Here's an example. It uses a table variable to show sample outputs. If your satisified with it, change it to use your actual table.

Declare @Temp Table(id int,start datetime, endTime datetime, Duration decimal(5,2))

Set DATEFORMAT DMY
Insert Into @Temp Values(1,'18/07/2005 18:00:00','19/07/2005 04:30:00',10.30)
Insert Into @Temp Values(2,'18/07/2005 09:00:00','18/07/2005 17:30:00',8.30)

Select Id, 
       Start, 
       DateAdd(day, DateDiff(day, 0, Start), 1) As EndTime,
       Convert(varchar(5), DateAdd(day, DateDiff(day, 0, Start), 1) - Start, 108) as Duration,
       1.0 * DateDiff(minute, Start, DateAdd(day, DateDiff(day, 0, Start), 1)) / 60 As DurationInHours
From   @Temp A
Where  DateDiff(day, 0, Start) <> DateDiff(day, 0, EndTime)

Union ALL

Select Id, 
       DateAdd(day, DateDiff(day, 0, Start), 1),
       EndTime,
       Convert(varchar(5), EndTime - DateAdd(day, DateDiff(day, 0, Start), 1), 108) as Duration,
       1.0 * DateDiff(minute, DateAdd(day, DateDiff(day, 0, Start), 1), endTime) / 60
From   @Temp A
Where  DateDiff(day, 0, Start) <> DateDiff(day, 0, EndTime)

Union All

Select Id, 
       Start,
       EndTime,
       Convert(varchar(5), EndTime - Start, 108) as Duration,
       1.0 * DateDiff(minute, Start, endTime) / 60
From   @Temp A
Where  DateDiff(day, 0, Start) = DateDiff(day, 0, EndTime)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rjohn2001]

Thanks George. This is brilliant. Works great. I am tidying up my statements and will let you know if i need any help from you.

I just made one change to your statement

dateadd(minute,-1,DateAdd(day, DateDiff(day, 0, Start), 1))

substracted 1 min to get the datetime as

18/07/2005 23:59:00