SQL Server Performance

Julian date to Proper date

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, May 30, 2007.

  1. Madhivanan Moderator

    This function will convert Julian dates to proper dates

    CREATE function julian_to_date(@julian varchar(25))
    returns datetime
    as
    Begin
    declare @date datetime

    select @julian=substring(@julian,1,7)
    if len(@julian)=7 and @julian not like '%[^0-9]%'
    select @date=dateadd(day,cast(right(@julian,3) as int)-1,cast(left(@julian,4) as datetime))
    else
    select @date=null

    return @date
    end

    Ex

    Select
    dbo.julian_to_date('2006128')


    Madhivanan

    Failing to plan is Planning to fail
  2. satya Moderator

  3. FrankKalis Moderator

    You should be able to rewrite this from:


    dateadd(day,cast(right(@julian,3) as int)-1,cast(left(@julian,4) as datetime))

    to:


    dateadd(day, (@julian % 1000) -1, LTRIM(@julian / 1000))


    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  4. Madhivanan Moderator

    Thanks Frank [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page