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

How to register a business name

Attending business summits and conferences is a big help to those who belong to the quite "techy" (technological or technical) industry. Being a graduate of one, I had less knowledge in the field of entrepreneurship. Enrolling myself in business administration gave me quite the knowledge to be a part of the business world and thus improve my entrepreneurial skills. I now would like to share this information that I got familiar with (and I managed to get a copy of the entire process from the 6th Mindanao ICT Congress): How to Register a Business Name (in the Philippines) ----------------------------------------------------------------------------------------------- SINGLE PROPRIETORSHIP Applicant must secure 2 copies of registration form and pay Php 300.00 (rate may change) for single proprietorship registration processing fee. The registration shall be valid for five (5) years. A surcharge of Php 100.00 is imposed if renewal is filed beyond the three (3) month grace period, c...

Adding a Footer to the DataGridView component

I have been searching for sites and forums that would give me a any hint on having a footer on the .net DataGridView control. It was frustrating. I found some, but not what I was looking for. I use windows forms. It would have been easier if I was into web. I decided to create one for myself. It's not complete, but it works with me. It needs improvement and I hope that some programmers who might pass through this blog will help me with it :D. Limitations: Cannot set Footer values during design time. Can sometimes hide a row when scrolled to the last item in the grid. What I did was just create a user control that inherits the DataGridView control and add a StatusStrip to act as the footer. public partial class MyDataGridView : DataGridView { public StatusStrip Footer { get { return (StatusStrip)this.Controls["Footer"]; } } private bool _footerVisible; [Browsable(false)] /// /// Sets or Gets the va...

Using Crystal Reports 10 with C#.net and Firebird

C# express doesn't include a report designer or viewer. Reports however, is very much needed when creating a business software. Since C# express doesn't include a report designer, we need to find other means. One is to use a free report such as MyNeoReport. This however may not work under many circumstances. The other alternative would be to use a proven report engine and designer-Crystal Report. Crystal Report has been used by many developers (in our city). However, using a free programming language and IDE, and a free database is very limiting. Not much information can be gathered on the net either (with regards to reporting as of this writing). Here's a way to use Crystal Reports using Firebird database and C# Express as software development IDE: Pre-requisites: C# Express 2005 EMS SQL Manager 2005 for InterBase & Firebird Lite Crystal Reports 10 Create the following database: Name: TestDB1 Tables: TESTTABLE1 Columns: ID - PK, INTEGER,AUTOINCREMENT DES...

RixxTech

Search This Blog