none
Julian Date to Date(time) Conversion RRS feed

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 9, 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

  • 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 8, 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 9, 2014 1:40 AM
  • A common situation in which this arises is Julian date as part of a file name. File names are strings.

    Gardoglee

    Wednesday, February 18, 2015 10:58 AM
  • Deleted
    Saturday, October 27, 2018 3:36 AM