Results 1 to 8 of 8

Thread: Julian Date

  1. #1
    Join Date
    Feb 2004
    Posts
    20

    Unanswered: Julian Date

    A table that I am querying from has the date field in julian date format "04194". I am only wanting to pull the information from this table that is => than todays date. Maybe some of you have done this before and can give a little help in finding the best way to do this?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You could take today's date and subtract it from the first day of the year (DATEDIFF ( datepart , startdate , enddate ) ) to give you an int value for the day of the year (actually day of year -1 since 20040101 - 20040101 = 0). Then convert to a 3 character value and concatinate the two digit year to the front and ... tadaaa ... the julian date.

  3. #3
    Join Date
    Feb 2004
    Posts
    20
    ? I am not following you?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can get the Julian date using:
    Code:
    SELECT 1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate())
    If you need it in character format, you can use:
    Code:
    SELECT Replace(Str(
           1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate()), 5), ' ', '0')
    After you've got that, the rest should just be a simple compare.

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or you could go the other way so you can utilize all of SQL Server's functions

    Code:
    DECLARE @julian char(5), @gregorian datetime
    SELECT @julian = '04194'
    SELECT @gregorian = DATEADD(dd,CONVERT(int,SUBSTRING(@julian,3,3)),CONVERT(datetime,'20'+SUBSTRING(@julian,1,2)+'/01/01'))
    SELECT DATEDIFF(dd,GetDate(),@gregorian)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Posts
    20
    Perfect!...Thanks for your help. I was going about it the wrong way. I was trying to convert the julian date and then compare.

  7. #7
    Join Date
    Feb 2004
    Posts
    20
    Thanks also Brett That was a road I was eventually going to have to cross.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    udf's....Makes a perfect house warming gift

    Code:
    CREATE FUNCTION udf_JulianToGregorian(@julian char(5))
    RETURNS datetime
    AS
    BEGIN
    	DECLARE @gregorian datetime
    	SELECT @gregorian = 
    		DATEADD(dd,CONVERT(int,SUBSTRING(@julian,3,3))
    		,CONVERT(datetime
    		,CASE WHEN SUBSTRING(@julian,1,2) BETWEEN '00' AND '50' 
    		      THEN '20'
    		      ELSE '19'
    		 END
    		+SUBSTRING(@julian,1,2)+'/01/01'))
    	RETURN @gregorian
    END
    GO
    
    DECLARE @julian char(5)
    SELECT @julian = '04194'
    SELECT dbo.udf_JulianToGregorian(@julian)
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •