Working with Time Frames in T-SQL

Question

Working with Time Frames in T-SQL

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Nice article. I like Ryan's elegant solution - unfortunately many of our clients are still sticking with SQL 2k (even though they could get by with Express) so I haven't played with CTEs....

The discussion about 59.59 or 60.00 in an hour is much the same as people who call midday 12am rather than 12pm, which day is 12am a part of, etc... (I know the answers, as you all would too, so no need to tell me )

Only correction is that situation #6's description is wrong - the before/after is mixed up...

Dave F-425609 Right there with Babe Points: 750 More actions · Answer 2

Hi,

I didn't read the whole article, but had a couple on the more basic points.

SELECT

COUNT(*)

FROM

[MY_EVENTS]

WHERE

---CASES 3, 4 AND 5

(([STARTING_TIME] > 7/1/2007 12:00 PM AND [STARTING_TIME] < 7/1/2007 1:00 PM)

OR ([ENDING_TIME] > 7/1/2007 12:00 PM AND [ENDING_TIME] < 7/1/2007 1:00 PM))

OR

---CASE 2

([STARTING_TIME] < 7/1/2007 12:00 PM AND [ENDING_TIME] > 7/1/2007 1:00 PM)

This could be written much more simply as

SELECT

COUNT(*)

FROM

[MY_EVENTS]

WHERE [STARTING_TIME] <= 7/1/2007 1:00 PM

AND [ENDING_TIME] >= 7/1/2007 12:00 PM --This assumes that starting_time<ending_time

Also to get the time period utilised for each row you could do something like

(((7/1/2007 1:00PM)-(START_TIME)-(CASE END_TIME<(7/1/2007 1:00PM) THEN ((7/1/2007 1:00PM)-(END_TIME)) ELSE 0))%60)*24*60

to get the number of minutes

Not criticising, it's just what I thought of what I began, and couldn't continue until I wrote it down

steve smith-401573 SSChampion Points: 10889 More actions · Answer 3

what format would you like us to use? dd month year, without any punctuation at all? (example: today is 17 August 2007)

the short date format, using two digits for month, day and year, is only ambiguous during the first 12 days of the month, when the order is not intuitively obvious (assuming a valid date). given that the average month is 365/12 or 30.42 days long, then ambiguity can only occur 39.5% of the time. Which is much greater precision than one gets in the form of specifications from an end user.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 4

Robert - obviously you've never dealt with psychotherapists. Their hours range from 45 to 50 minutes.

Me Da Man SSC Enthusiast Points: 188 More actions · Answer 5

What about the situation where the Start date/times match? What about the situation where the Start and End date/times match? How does this affect the WHERE clause of the first query?

Thanks