SQL Server Performance

Convert to Julian Date

Discussion in 'General Developer Questions' started by Madhivanan, Jan 18, 2006.

  1. Madhivanan Moderator

    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
  2. satya Moderator

  3. FrankKalis Moderator

    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
  4. Madhivanan Moderator

    Satya, I already referred first link that gives the value 38733<br />I also referred Brett's and he used the same logic of the links that I posted in my question.<br />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=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. FrankKalis Moderator

    Correction!<br />Seems like I've got one 0 too much <br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT<br /> YEAR(GETDATE())*100<br /> +<br /> DATEPART(y,GETDATE()) AS the_date<br /></font id="code"></pre id="code"><br />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=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  6. Madhivanan Moderator

    Well Frank. I think I have got answer there [8D]<br /><br />Im also referring these which explains the logic<br /<a target="_blank" href=http://aa.usno.navy.mil/data/docs/JulianDate.html>http://aa.usno.navy.mil/data/docs/JulianDate.html</a><br /<a target="_blank" href=http://scienceworld.wolfram.com/astronomy/JulianDate.html>http://scienceworld.wolfram.com/astronomy/JulianDate.html</a><br /><br />&gt;&gt;I really think it's an annoying habit to ask the same question in many places. <br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. satya Moderator

    <i> I really think it's an annoying habit to ask the same question in many places. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]</i><br />Well, its a human tendency to ignore the checkup process... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  8. FrankKalis Moderator

  9. Madhivanan Moderator

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

Share This Page