a Daylight savings time aware version of a sqlclr xp_getfiledetails procedure.

One of the undocumented extended stored procedures that no longer exists in SQL Server 2005 is xp_getfiledetails.

Of course I am aware that I am not the first one to implement this procedure as a SQLCLR procedure.

I found implementations on the following locations:

• SSIS Junkie Blog by Jamie Thomson
• Simple Talk by Greg Larsen

But a bug I noticed in both the versions of the procedure is the fact that it is not "Daylight Savings Time" aware.

This means that if you request the file details of a file that is created on a day when Winter Time was in place and currently Summer Time is in use that the timestamps are off 1 hour.

To correct this problem this I wrapped the datetime checks in the following function:

private static TimeSpan ExplorerBias(DateTime timeStamp)
{
    TimeSpan bias = new TimeSpan(0);
    if (TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now) != TimeZone.CurrentTimeZone.IsDaylightSavingTime(timeStamp))
    {
        bias = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now) - TimeZone.CurrentTimeZone.GetUtcOffset(timeStamp);
    }
    return bias;
}

You can download the Complete xp_getfiledetails sqlclr Script and see the effect of the function.

To test the script, create a new Visual Studio 2005 Database project and paste the code from the script (or just include the file.)