The Archives


I’m Attending


Tag Archives: Database

Converting Seconds to HH:MM:SS Format in SQL

Posted in: Programming by Steve on October 17, 2010

In our web application, we needed to display a duration that is stored in seconds as hours, minutes and seconds.  We had a way to do this in our .NET code, but we needed this for a report that is rendered using reporting services.  We needed to find an easy way to display this in the report.

For example, if we have a field returning 5578 seconds, we want it to display as 1 hour, 32 minutes and 58 seconds with this format: 01:32:58

My manager found this nifty little conversion available in SQL Server 2005:

DECLARE @seconds INT
SET @seconds = 5578

SELECT CONVERT(varchar, DATEADD(s, @seconds, 0), 108) --Result is 01:32:58

Note that this only works if the duration is less than 24 hours. If you use this on a duration longer than 24 hours, it will reset at 00:00:00.

.NET Migrations and Legacy Databases

Posted in: Programming by Steve on October 10, 2010

Trying to get our web application database migrations has been trying to say the least. I have been investigating which one of the .NET migration strategies will work best for our application.

Ben Scheirman has a blog post that has a list of tools available for .NET developers to do migrations.  It is a good starting point to look into how you can get your app under control.

FluentMigrator has just reached 1.0 RC1 and looks to be very promising.

Subtracting a record count from current records

Posted in: Programming by Steve on September 25, 2010

In our application, we have a need to archive some specific records in a table.  After the records are archived, more records could potentially be added after those that are not archived.  Here is an example table:

ID    |   InstanceNumber     |   IsArchived

5000  |         1            |     True
8347  |         2            |     True
9343  |         3            |     False
11048 |         4            |     False
15311 |         6            |     False

So the record set I am trying to return is this:

ID    |   InstanceNumber     |   IsArchived

9343  |         1            |     False
11048 |         2            |     False
15311 |         4            |     False

This results in records that are not archived, but with Instance Numbers that have been adjusted by subtracting the count of archived records. The SQL that I came up with is this:


INSERT INTO @tbl VALUES (5000, 1, 1)
INSERT INTO @tbl VALUES (8347, 2, 1)
INSERT INTO @tbl VALUES (9343, 3, 0)
INSERT INTO @tbl VALUES (11048, 4, 0)
INSERT INTO @tbl VALUES (15311, 6, 0)

SELECT ID, InstanceNumber - (SELECT MAX(InstanceNumber) FROM @tbl WHERE IsArchived = 1), IsArchived
FROM @tbl
WHERE IsArchived = 0

I am looking to optimize this query so it does not have any adverse affect on the report that it is needed for.

Any ideas? I even added a StackOverflow question about it.

Combining Date and Time in SQL Server

Posted in: Programming by Steve on March 5, 2009

I have been working with Log Parser to go through IIS data from the last week. In working with it, I found that the queries I wanted to run were a little obscure to me, and not having time to mess around with learning a new query language, I just imported the data into SQL.

The W3C logs store the date and time in separate columns, and for one of my queries, I wanted to combine the date part with the time part to create a single datetime field. 

So here’s my function that I created:

CREATE FUNCTION [dbo].[CombineDateTime](@dtDate DATETIME, @dtTime DATETIME)
    RETURN @dtDate + CONVERT(CHAR(8), @dtTime, 108)

Hope this helps!