Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Split Date which straddles at midnight?

Status
Not open for further replies.

rjohn2001

Programmer
Nov 21, 2005
24
GB
Hi guys


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

here is my data

Code:
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

 
>> 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
 
Thanks George, I am out of my mind. Here is the modified sample data.

Code:
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..
 
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.

Code:
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
 
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

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

substracted 1 min to get the datetime as

18/07/2005 23:59:00



 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top