SQL Server Performance Forum – Threads Archive

Convert to Julian Date

Oracle has convert function that converts Normal date to Julian date SELECT TO_CHAR(TRUNC(SYSDATE),’J’) FROM DUAL; This returns the value 2453754 for the date 18 Jan,2006 I think there is no function in SQL Server to convert to Julian date When I searched I found following
http://support.microsoft.com/default.aspx?scid=kb;en-us;116281
http://www.minisolve.com/TipsJulianSQL.htm But according to those links the Converted Julian date is 106108
I dont understand the logic used in the Oracle How do I use the same logic in SQL Server?
Madhivanan Failing to plan is Planning to fail

http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-2-udf_DT_ToJulian.htm &http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I would think that the correct answer is 2006018, as satya’s second link also yields.
Btw, Brett’s solution there can be simplified to
SELECT
YEAR(GETDATE())*1000
+
DATEPART(y,GETDATE()) AS the_date
GO I don’t know what logic Oracle uses, and unless you can’t tell us, we can’t think of a way to implement it into SQL Server. Here’s a brilliant link about dates and calendars:http://www.tondering.dk/claus/cal/ —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Satya, I already referred first link that gives the value 38733 I also referred Brett’s and he used the same logic of the links that I posted in my question. But none match with the value that Oracle returns and I need the logic used in Oracle [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Correction! Seems like I’ve got one 0 too much <pre id="code"> SELECT YEAR(GETDATE())*100 + DATEPART(y,GETDATE()) AS the_date </pre id="code"> Btw, check out your MS newsgroup thread. You’ve got your answer there. I really think it’s an annoying habit to ask the same question in many places. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Well Frank. I think I have got answer there [8D] Im also referring these which explains the logic http://aa.usno.navy.mil/data/docs/JulianDate.html</a> http://scienceworld.wolfram.com/astronomy/JulianDate.html</a> >>I really think it’s an annoying habit to ask the same question in many places. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

I really think it’s an annoying habit to ask the same question in many places. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Well, its a human tendency to ignore the checkup process… [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. <hr noshade size="1">Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

While I agree, that newbies often exhibit this behaviour, advanced users should really refrain from doing so. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Well Frank. I rarely do that [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>