Dear All,

Can anyone let me know how to convert a Julian date which is this format "730511" to "DD-MM-YYYY" in Sql Server.

Thanks for your help in advance.

Kind Regards
Praveen Kumar


Praveen Motupally

Praveen Motupally

Praveen,
Here find the VB function which do opposite to your demand, but at least you can get an idea and convert into what you want.
-ned

Public Function JulianDate(ByVal datDate As Date) As Double

    Dim GGG
    Dim DD, MM, YY
    Dim S, A
    Dim JD, J1

    Let MM = Month(datDate)
    Let DD = Day(datDate)
    Let YY = Year(datDate)
    Let GGG = 1

    If (YY <= 1585) Then
        GGG = 0
    End If

    Let JD = -1 * Int(7 * (Int((MM + 9) / 12) + YY) / 4)
    Let S = 1

    If ((MM - 9) < 0) Then
        S = -1
    End If

    Let A = Abs(MM - 9)
    Let J1 = Int(YY + S * Int(A / 7))
    Let J1 = -1 * Int((Int(J1 / 100) + 1) * 3 / 4)
    Let JD = JD + Int(275 * MM / 9) + DD + (GGG * J1)
    Let JD = JD + 1721027 + 2 * GGG + 367 * YY

    If ((DD = 0) And (MM = 0) And (YY = 0)) Then
      MsgBox "Please enter a meaningful date!"
    Else
      Let JulianDate = JD
    End If

Exit Function

JulianDate_Error:
    MsgBox "MainModule.JulianDate()"
    Exit Function
    Resume

End Function

The Date Code you are referring to is not true julian date it is known as 'Rata Die' to achieve julian date(7 Figure datecode) use the formula as follows.

RD = JD - 1721424.5

so

JD = RD + 1721424.5



Hope this Helps

Simon

1. Julian Date number is on offset of days from noon on January 1, 4713 BC to noon on any day.
2. Modified Julian Date number is an offset from midnight (00:00:00) on 1858/11/17 to midnight (00:00:00) on any day.

SELECT DATEADD(day, 730511, '1858/11/17') gives

Dec 13, 3858