Tag Archives: Database
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.
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.
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:
DECLARE @tbl TABLE (ID INT NOT NULL, InstanceNumber INT NOT NULL, IsArchived BIT NOT NULL) 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.
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) RETURNS DATETIME AS BEGIN RETURN @dtDate + CONVERT(CHAR(8), @dtTime, 108) END
Hope this helps!