Post reply

convert julian (not so true) date to normal date format

  • April 25, 2007 at 4:43 pm

    #172097

    Hi,

    I have a column that is being called a julian date format although is not "true" julian date and I need to convert them to regular date YYMMDD.  An example would be 6339 which is referring to Dec 7, 2006.  In Oracle, I would use:

    to_char(to_date(julian_col,'YDDD'),'YYMMDD') as REGULAR_DATE.

    I tried using the convert in SQL and I'm not sure how to do it. 

    CONVERT(datetime,lt.lot_code,101) as REGULAR_DATE and for 6339, I get back 6339-01-01 00:00:00.000.  What I need to get back is 061207, without the timestamp part.  Can someone give me a few pointers to wrap my Oracle brain around?

    Thank you!!

    Isabelle

    Thanks!
    Bea Isabelle

  • April 25, 2007 at 6:00 pm

    #702511

    This code has a built in assumption that the year 6 is in decade 2000 to 2009.  You didn't really say what your rules are for knowing the decade, so I will let you change that if the rule is different.

    If you are curious about what the functions are doing, you can read about them in SQL Server Books Online.

    There are links to many other SQL Server date/time resources here:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

     

    select
 OutDate = convert(varchar(6),aa.Date,12),
 aa.Date
from
 (
 select
  -- Convert to a date time
  Date = dateadd(yy,(MyJulian/1000)+100,MyJulian%1000)
 from
  ( select MyJulian = 6339 ) a
 ) aa
    Results:
    OutDate Date
------- ------------------------
061206  2006-12-06 00:00:00.000
    (1 row(s) affected)
     
     

     

  • April 26, 2007 at 9:42 am

    #702659

    Thank you.  I have been reading alot of Books online and getting ideas from the examples but it is difficult to try and put it all together and make it work right.  But I guess that's the way you learn!   Your example is great and I will read up on the topics that you sent as well.

    Thanks again!

    Isabelle

    Thanks!
    Bea Isabelle

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply