Time range in SQL Server

Sometimes we encounter situations wherein we need to include in our query, a time range given that the only fields in the SQL server database is the start time and the duration:

StartTime Duration
09:30:00 AM 60
07:30:00 AM 90
*The table name in this example is tbl_UserSchedule


We sometimes want to show it in a form of a time range just like looking at a schedule:
Schedule
09:30:00 AM - 10:30:00 AM
07:30:00 AM - 09:00:00 AM

The following SQL statement does the job (although not quite optimized):
SELECT
CONVERT(char(9), StartTime, 108) +
CASE WHEN StartTime > '11:59:59 PM'
THEN 'PM' ELSE 'AM' END
+ ' - ' +
CONVERT(char(9), DATEADD(minute,
Duration, StartTime), 108) +
CASE WHEN DateAdd(minute, Duration, StartTime) > '11:59:59 PM'
THEN 'PM' ELSE 'AM' END

FROM tbl_UserSchedule

Comments

0 Responses to "Time range in SQL Server"