Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    35

    Unanswered: SQL - Day Of Year (CYYDDD) to Datetime (MM/DD/YYYY) Conversion

    Our ERP Package stores dates in the "Day Of Year" (not Julian which is YYDDD) format, which is CYYDDD. I am trying to write an Transact SQL Function that will convert the "Day of Year" to a regular datetime in SQL Server (MM/DD/YYYY).

    It would also have to be able to correctly convert on a leap year. Any ideas on how to do this?

    http://=http://daac.gsfc.nasa.gov/ju... Year Calendar

    Database = SQL Server 2005
    C = Century
    YY = Year
    DDD = Day

    Thanks in advance and I will continue to research.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'll assume you use C=1 for the 1900s and C=2 for the 2000s
    Code:
    CREATE TABLE test_julians
    ( cyyddd CHAR(6)
    );
    INSERT INTO test_julians VALUES ( '100060' );
    INSERT INTO test_julians VALUES ( '184060' );
    INSERT INTO test_julians VALUES ( '194060' );
    INSERT INTO test_julians VALUES ( '200060' );
    INSERT INTO test_julians VALUES ( '204060' );
    INSERT INTO test_julians VALUES ( '209225' );
    
    SELECT cyyddd
         , DATEADD(D,SUBSTRING(cyyddd,4,3)-1  -- add "ddd" days minus one
              , CONVERT(DATETIME
                  , CASE WHEN LEFT(cyyddd,1) = '1'  -- check century
                         THEN '19'
                         ELSE '20' END
                       + SUBSTRING(cyyddd,2,2) + '-01-01') ) AS new_date
      FROM test_julians ;
    
    
    cyyddd new_date            
    ------ ------------------- 
    100060 1900-03-01 00:00:00
    184060 1984-02-29 00:00:00
    194060 1994-03-01 00:00:00
    200060 2000-02-29 00:00:00
    204060 2004-02-29 00:00:00
    209225 2009-08-13 00:00:00
    
    6 row(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    35
    r937, thanks so much for your help.

    This was exactly what I needed.

    Mike

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    Code:
    SELECT	cyyddd,
    	DATEADD(DAY, [Days], Jan1)
    FROM	(
    		SELECT	cyyddd,
    			cyyddd % 1000 - 1 AS [Days],
    			DATEADD(MONTH, 12 * (cyyddd / 1000) - 1200,  0) AS Jan1
    		FROM	test_julians
    	) AS d
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice one, peso

    you have an implicit datatype conversion buried in there (performing arithmetic on a string) but then i think i had an extra one too, although i do try to use CAST to make them explicit

    what would your query look like if c=0 for 1900's and c=1 for 2000's?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2007
    Posts
    183
    Drop the minus 1200...

    Code:
    CREATE TABLE test_julians
    ( cyyddd int
    );
    INSERT INTO test_julians VALUES ( 100060 );
    INSERT INTO test_julians VALUES ( 184060 );
    INSERT INTO test_julians VALUES ( 194060 );
    INSERT INTO test_julians VALUES ( 200060 );
    INSERT INTO test_julians VALUES ( 204060 );
    INSERT INTO test_julians VALUES ( 209225 );
    
    SELECT	cyyddd,
    	DATEADD(DAY, [Days], Jan1)
    FROM	(
    		SELECT	cyyddd,
    			cyyddd % 1000 - 1 AS [Days],
    			DATEADD(MONTH, 12 * (cyyddd / 1000) - 1200,  0) AS Jan1
    		FROM	test_julians
    	) AS d
    
    truncate table test_julians
    
    INSERT INTO test_julians VALUES ( 000060 );
    INSERT INTO test_julians VALUES ( 084060 );
    INSERT INTO test_julians VALUES ( 094060 );
    INSERT INTO test_julians VALUES ( 100060 );
    INSERT INTO test_julians VALUES ( 104060 );
    INSERT INTO test_julians VALUES ( 109225 );
    
    SELECT	cyyddd,
    	DATEADD(DAY, [Days], Jan1)
    FROM	(
    		SELECT	cyyddd,
    			cyyddd % 1000 - 1 AS [Days],
    			DATEADD(MONTH, 12 * (cyyddd / 1000),  0) AS Jan1
    		FROM	test_julians
    	) AS d
    
    drop table test_julians
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

Posting Permissions

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