Here is the second post that has to do with converting a julian date to a datetime  This only works for dates greater than October 15th 1582 at 12:00:00.

ALTER FUNCTION [dbo].[fnConvertJulianToDate]
(
 @JD DECIMAL(18,5)
)
RETURNS DATETIME
AS
BEGIN

 DECLARE @j INT
 DECLARE @g INT
 DECLARE @dg INT
 DECLARE @c INT
 DECLARE @dc INT
 DECLARE @b INT
 DECLARE @db INT
 DECLARE @a2 INT
 DECLARE @da INT
 DECLARE @y2 INT
 DECLARE @m2 INT
 DECLARE @d INT
 DECLARE @year INT
 DECLARE @month INT
 DECLARE @day INT
 DECLARE @hr INT
 DECLARE @min INT
 DECLARE @sec INT
 DECLARE @frac DECIMAL(18,5)
 DECLARE @f DECIMAL(18,2)
 DECLARE @finalDateTime DATETIME

 SET @frac =  @JD - FLOOR(@JD) + 0.5

 IF(@frac >= 1.0)
  BEGIN
   SET @frac = @frac - 1.0
   SET @JD = @JD + 1.0
  END

 SET @j = FLOOR(@JD) + 32044
 SET @g = @j / 146097
 SET @dg = @j % 146097
 SET @c = (@dg / 36524 + 1) * 3 / 4
 SET @dc = @dg - @c * 36524
 SET @b = @dc / 1461
 SET @db = @dc % 1461
 SET @a2 = (@db / 365 + 1) * 3 / 4
 SET @da = @db - @a2 * 365
 SET @y2 = @g * 400 + @c * 100 + @b * 4 + @a2
 SET @m2 = (@da * 5 + 308) / 153 - 2
 SET @d = @da - (@m2 + 4) * 153 / 5 + 122
 SET @year = @y2 - 4800 + (@m2 + 2) / 12
 SET @month = (@m2 + 2) % 12 + 1
 SET @day = @d + 1.5

 SET @hr  = FLOOR(@frac * 24.00)
 SET @min  = FLOOR((@frac*24.00 - @hr)*60.00)
 SET @f  = ((@frac*24.00 - @hr)*60.00 - @min)*60.00
 SET @sec = FLOOR(@f)
 SET @f = @f - @sec

 IF( @f > 0.5 )
  BEGIN
   SET @sec = @sec + 1
  END

 SET @finalDateTime = CONVERT(VARCHAR(20), CAST(@month AS VARCHAR(2))
    + '/'
    + CAST(@day AS VARCHAR(2))
    + '/'
    + CAST(@year AS VARCHAR(4))
    + ' '
    + CAST(@hr AS VARCHAR(2))
    + ':'
    + CAST(@min AS VARCHAR(2))
    + ':'
    + CAST(@sec AS VARCHAR(2)))

 RETURN @finalDateTime

END

Again here are the reference for the math and testing

http://aa.usno.navy.mil/data/docs/JulianDate.php
http://www.onlineconversion.com/julian_date.htm
http://en.wikipedia.org/wiki/Julian_day

Also to test it on your SQL Server just run the following queries once the functions are made

SELECT dbo.fnConvertDateToJulian(getDate())

SELECT dbo.fnConvertJulianToDate(dbo.fnConvertDateToJulian(getDate()))

I had to deal with converting julian dates to gregorian dates (normal date time) and vice versa recently so I wrote 2 user functions that handle converting them back and forth.  This is the first post that deals with converting a DateTime to a Julian Date.

ALTER FUNCTION [dbo].[fnConvertDateToJulian]
(
 @dtDate DATETIME
)
RETURNS DECIMAL(18,5)
AS
BEGIN

 DECLARE @a INT
 DECLARE @y INT
 DECLARE @m INT
 DECLARE @JDN INT --Julian Day Number
 DECLARE @JD DECIMAL(18,5) --Full Julian Date
 DECLARE @dayOfWeek INT
 DECLARE @date DATETIME

 SET @date = @dtDate
 SET @dayOfWeek = DATEPART(dd,@date)

 SET @a = FLOOR((14 - DATEPART(mm, @date)) / 12)
 SET @y = DATEPART(yy, @date) + 4800 - @a
 SET @m = DATEPART(mm, @date) + (12 * @a) - 3
 SET @JDN = FLOOR(@dayOfWeek + ((153 * @m + 2) / 5) + (365 * @y) + (@y / 4) - (@y / 100) + (@y / 400) - 32045)
 SET @JD = @JDN + ((DATEPART(hh, @date) - 12.00) / 24.00) + (DATEPART(mi,@date) / 1440.00) + (DATEPART(ss, @date) / 86400.00)

 RETURN @JD

END

Below are the references I used to test and get the math for it:

http://aa.usno.navy.mil/data/docs/JulianDate.php
http://www.onlineconversion.com/julian_date.htm
http://en.wikipedia.org/wiki/Julian_day

Hey everyone I wanted to post some code I had sometime back when on how I pulled SQL stats from a SQL Server using VB.NET and ASP.NET.  The code that will be in this post is actually part of a class that handles the SQL stats, but you can incorporate it however you would like.  There are a few things required for this. 

1. This is written in .NET 2.0
2. You need to add Imports System.Diagnostics   to your class or aspx page
3. You also have to have the rights to pull system stats from your SQL Server.  What this means is that you either need to enable Indentity Impersonate on the web application and make sure whoever uses the app has rights, or run the web application under an Application Pool in IIS that has access to the SQL Servers.
4. You'll see a variable in this class Me._Server.  Replace this with the server name you are trying to pull stats from or create a variable names _Server as a String
5. Add a reference to the COM object Microsoft SQLDMO Object Library 

Ok so here is some code that should get you by for a simple SQL Monitoring tool.  Again you can use this wherever you want in your app.  I had mine in a seperate class.

======================================================

Private Function getSQLStatus() As String

        Dim objSrv As New SQLDMO.SQLServer

        objSrv.LoginSecure = True
        objSrv.Connect(Me._Server)

        Dim tmpStatus As String = String.Empty

        Select Case objSrv.Status

            Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Continuing
                tmpStatus = "Continuing"
            Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused
                tmpStatus = "Paused"
            Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Pausing
                tmpStatus = "Pausing"
            Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running
                tmpStatus = "Running"
            Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Starting
                tmpStatus = "Starting"
            Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped
                tmpStatus = "Stopped"
            Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopping
                tmpStatus = "Stopping"
            Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Unknown
                tmpStatus = "Unknown"
        End Select

        objSrv.Close()
        Return tmpStatus

    End Function

    Private Function getSQLVersion() As String

        Dim objSrv As New SQLDMO.SQLServer

        objSrv.LoginSecure = True
        objSrv.Connect(Me._Server)

        Dim tmpString As String = objSrv.VersionString.Substring(0, 39)

        objSrv.Close()
        Return tmpString

    End Function

    Private Function getSQLDBCount() As String

        Dim tmpDBCount As String

        Dim objSrv As New SQLDMO.SQLServer

        objSrv.LoginSecure = True
        objSrv.Connect(Me._Server)

        tmpDBCount = objSrv.Databases.Count

        objSrv.Close()

        Return tmpDBCount

    End Function

    Private Function getSQLCurrCons() As String

        Dim tmpConns As String
        Dim tmpVal As New PerformanceCounter("SQLServer:General Statistics", "User Connections", Nothing, Me._Server)

        tmpConns = tmpVal.RawValue

        Return tmpConns

    End Function

    Private Function getSQLActiveTransSec() As String

        Dim sample1 As CounterSample
        Dim sample2 As CounterSample
        Dim tmpTrans2 As Double

        Dim cntVal As New PerformanceCounter("SQLServer:Databases", "Transactions/sec", "_Total", Me._Server)

        sample1 = cntVal.NextSample()
        System.Threading.Thread.Sleep(1000)
        sample2 = cntVal.NextSample()

        tmpTrans2 = CounterSample.Calculate(sample1, sample2)
        tmpTrans2 = System.Math.Round(tmpTrans2, 0)

        Return tmpTrans2.ToString()

    End Function

    Private Function getSQLMemGrants() As String

        Dim cntVal As New PerformanceCounter("SQLServer:Memory Manager", "Memory Grants Pending", Nothing, Me._Server)

        Return cntVal.NextValue()

    End Function

    Private Function getSQLTotalMem() As String

        Dim cntVal As New PerformanceCounter("SQLServer:Memory Manager", "Total Server Memory (KB)", Nothing, Me._Server)

        Return FormatNumber(((UInt64.Parse(cntVal.NextValue.ToString()) / 1024)), 0)

    End Function

    Private Function getSQLAvgLockWaitTime() As String

        Dim cntVal As New PerformanceCounter("SQLServer:Locks", "Average Wait Time (ms)", "_Total", Me._Server)

        Return FormatNumber(UInt64.Parse(cntVal.NextValue.ToString()), 0)

    End Function

    Private Function getSQLBufferCacheRatio() As String

        Dim cntVal As New PerformanceCounter("SQLServer:Buffer Manager", "Buffer cache hit ratio", Nothing, Me._Server)

        Return FormatNumber(cntVal.NextValue(), 0)

    End Function

    Private Function getSQLLocks_Sec() As String

        Dim cntVal As New PerformanceCounter("SQLServer:Locks", "Lock Waits/sec", "_Total", Me._Server)

        Return cntVal.NextValue()

    End Function

 ==================================================

Again hope this helps those that need to gather sql stats in a quick manner or need to monitor a sql server for stat purposes / high overview of usage. 

Unfortunately, in the Microsoft SQL world, sometimes you have to deal with using OPENQUERY.  This is difficult to use sometimes because you don't have as much flexibility with variables and parameters as you would using normal T-SQL.  Below is an example of how you can use OPENQUERY and still utilize a variable or parameter.

DECLARE @variable VARCHAR(10)
DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000)

SET @variable = 'yourStringValue'

SET @sqlQuery = 'SELECT * FROM tableName WHERE strValue = ' + '''' + '''' + @variable + '''' + ''''

SET @finalQuery = 'SELECT * FROM OPENQUERY(LINKEDSERVER,' + '''' + @sqlQuery + '''' + ')'

--used for debugging
SELECT @finalQuery

EXEC(@finalQuery)

NOTE: It may be hard to see but the '''' is 4 single quotes

I recommend commenting out EXEC(@finalQuery) until you see @finalQuery correctly in the results.  Running SELECT @finalQuery is very helpful because you can see what single quotes are missing if need be. 

I also recommend getting your OPENQUERY statement to work correctly before trying to make it "dynamic" so to say using the above method.

Anyway, I found this very useful to use and hope that it can help some others out there.

I wrote this code a while back when I needed a way for some people to restart a SQL Server when the IT team wasn't around.  This code was used in an ASP.NET app, but can easily be used in a windows form if necessary.

Note:  If you are going to use it in an ASP.NET app please make sure the application has the correct security context so it can actually restart the SQL Server services.  For example this code I used I made sure the app ran under and Application Pool identity that had access to restart the service.

This code was written in VB so sorry to those C# folks!

Ths is in the handler for btnRestart.Click

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Wmi
Imports Microsoft.SqlServer.Management.Common

Protected Sub btnRestart_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRestart.Click

        Dim str As String = Nothing

        'Declare and create an instance of the ManagedComputer object that represents the WMI Provider services.
        Dim mc As ManagedComputer
        mc = New ManagedComputer(txtServer.Text)

        'Iterate through each service registered with the WMI Provider.
        Dim svc As Service
        Dim svcAgent As Service
        For Each svc In mc.Services
            Response.Write(svc.Name & "<br />")
        Next

        'Reference the Microsoft SQL Server services.
        svc = mc.Services("MSSQLSERVER")
        svcAgent = mc.Services("SQLSERVERAGENT")

        Try
            'Stop the service if it is running and report on the status continuously until it has stopped.
            If svc.ServiceState = ServiceState.Running Then
              
                svc.Stop()
                Do Until svc.ServiceState = ServiceState.Stopped
                    str = str & String.Format("{0} service state is {1} on {2}<br />", svc.Name, svc.ServiceState, txtServer.Text)
                    Label2.Text = str
                    svc.Refresh()
                Loop

                'Start the service and report on the status continuously until it has started.
                svc.Start()
                Do Until svc.ServiceState = ServiceState.Running
                    str = Nothing
                    str = str & String.Format("{0} service state is {1} on {2}<br />", svc.Name, svc.ServiceState, txtServer.Text)
                    Label3.Text = str
                    svc.Refresh()
                Loop

                Label3.Text = str & String.Format("{0} service state is {1} on {2}<br />", svc.Name, svc.ServiceState, txtServer.Text)

                svcAgent.Start()
                If svcAgent.ServiceState = ServiceState.Running Then
                    Label4.Text = String.Format("{0} service state is {1} on {2}", svcAgent.Name, svcAgent.ServiceState, txtServer.Text)
                End If
                Do Until String.Format("{0}", svcAgent.ServiceState) = "Running"
                    str = str & String.Format("{0} service state is {1} on {2}<br />", svcAgent.Name, svcAgent.ServiceState, txtServer.Text)
                    Label4.Text = str
                    svcAgent.Refresh()
                Loop

            Else
                Label2.Text = "SQL SERVER IS NOT RUNNING."
            End If

        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try

    End Sub

Please notice that it takes the value from txtServer.Text.  This is the text box on the page that people will enter in the server name.

The one bug in this code is that since this is during one post back you actually don't see the label updating itself because it's not threading.

This usually takes about 1-2 minutes to perform depending on the server.

Again, this is useful for when you don't want to have to restart the server yourself and can have either someone beneath you do it, or someone you trust.