none
Julian Date to Date(time) Conversion

Answers

  • SELECT DATEADD(YEAR, @JulianDate / 1000 - 1900, @JulianDate % 1000 - 1)
    
    
    Friday, February 11, 2011 10:50 PM
  • And the other way around

    SELECT	DATENAME(YEAR, GETDATE()) + RIGHT('00' + DATENAME(DAYOFYEAR, GETDATE()), 3)
    
    Friday, February 11, 2011 11:00 PM
  • Calendar table look up. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, April 09, 2014 1:40 AM
  • I don't know what Microsoft would advise, but I have to wonder why you would store a julian date in a char column to begin with.  It seems like your best practices concern would disappear if you stored your julian data in an int column.

     

    Thinking about it another way, why use 7 bytes to store this value when an int only takes 4 bytes?

    Saturday, February 12, 2011 2:31 PM

All replies

  • SELECT DATEADD(YEAR, @JulianDate / 1000 - 1900, @JulianDate % 1000 - 1)
    
    
    Friday, February 11, 2011 10:50 PM
  • And the other way around

    SELECT	DATENAME(YEAR, GETDATE()) + RIGHT('00' + DATENAME(DAYOFYEAR, GETDATE()), 3)
    
    Friday, February 11, 2011 11:00 PM
  • As long as Microsoft supports implicit conversion, I think we're safe.
    Friday, February 11, 2011 11:31 PM
  • How about explicit conversion?
    Kalman Toth

    Slower than implicit?
    • Edited by SwePesoMVP Saturday, February 12, 2011 8:06 AM spelling error
    Saturday, February 12, 2011 5:52 AM
  • I understand that Peter. However, as a trainer I am concerned about Best Practices as well.

    What is the consensus of SQL experts about using char data type in division? What would Microsoft advise?  Thanks.

     I opened a new discussion thread on this topic:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/fe9123dc-728f-42c7-82c6-a2306aef156b


    Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM
    Saturday, February 12, 2011 2:09 PM
    Moderator
  • I don't know what Microsoft would advise, but I have to wonder why you would store a julian date in a char column to begin with.  It seems like your best practices concern would disappear if you stored your julian data in an int column.

     

    Thinking about it another way, why use 7 bytes to store this value when an int only takes 4 bytes?

    Saturday, February 12, 2011 2:31 PM
  • this thing worked . Thanks

    Tuesday, April 08, 2014 11:08 PM
  • Calendar table look up. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, April 09, 2014 1:40 AM