John Mendez

This simple user function will return the beginning of the day given a datetime in SQL. 

DECLARE @finalDate DATETIME

SET @finalDate = (SELECT CAST(DATEPART(mm,@date) AS VARCHAR(5)) + '/' + CAST(DATEPART(dd,@date) AS VARCHAR(5)) + '/' + CAST(DATEPART(yyyy,@date) AS VARCHAR(10)))

RETURN @finalDate

You can test it with the following:

Microsoft SQL sql 2005

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()))

Microsoft SQL julian date, sql 2005

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

Microsoft SQL julian date, sql 2005