Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    31

    Unanswered: get date from julianday

    Is there any transact sql function that allows you to get a date from a julian day value and a year. For example:

    day 20, year 2000 =1/20/04
    day 366, year 2004 = 12/31/2004

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    create function dbo.fn_GetDateFromJulian_2parameters (
       @julianday   int,
       @year		int					  ) returns datetime as
    begin
       declare @date_start datetime,
    		   @date_end datetime,
    		   @date_returned datetime,
    		   @diff int
       set @date_start = '01/01/' + ltrim(rtrim(cast(@year as varchar(10))))
       set @date_end = dateadd(day, -1, dateadd(year, 1, @date_start))
       set @diff = datediff(day, @date_start, @date_end)+1
       if @julianday > @diff set @date_returned = '01/01/1900'
       if @date_returned is null
    	  set @date_returned = dateadd(day, @julianday,
    		 dateadd(day, case when @julianday = 0 then 0 else -1 end, @date_start))
       return (@date_returned)
    end
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Couldn't you just use:
    Code:
    CREATE FUNCTION dbo.f_J2Date(
       @piYear INT -- Year
    ,  @piDOY  INT -- Day of the year
    ) RETURNS DATETIME AS BEGIN
       RETURN DateAdd(day, @piDOY - 1, Convert(VARCHAR(20), @piYear) + '-01-01')
    END
    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I could, and at first I did, but just wanted to add some intelligence to it, and some basic validation. Don't see anything wrong with that
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •