Post reply

convert todays date to julian format

  • May 2, 2011 at 9:12 am

    #145501

    Hello,

    Can some please tell me how to convert todays date to julian format

    Thank you

  • May 2, 2011 at 9:24 am

    #1318476

    ahh...but WHICH Julian format do you want?

    from lurking here on the forums, I've seem three different styles;

    for example

    109252 = in AS400/DB2 date is 01/01/1900 + 109 years + 252 days

    1997090101636 = 4 year Century + 090 julian days days + 10 hours + 16 minutes + 36 seconds

    199709014399 = 4 year Century + 090 julian days days + 14399 seconds

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 2, 2011 at 9:34 am

    #1318485

    The data is going to JDEdwards

  • May 2, 2011 at 9:41 am

    #1318492

    These scripts appear to work correctly, but do not return a time value.

    http://www.sqlservercentral.com/scripts/Miscellaneous/31225/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 2, 2011 at 9:56 am

    #1318506

    Krasavita (5/2/2011)

    The data is going to JDEdwards

    I think you'll have to check with them to know what specific format they expect before we can give you much of a code example.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 2, 2011 at 2:20 pm

    #1318725

    here's an example on how to get all the elements to create the Julian date; to append them you have to cast them to VARCHAR, and you'd also have to take into consideration whether you need preceding zeros and stuff (SELECT RIGHT('000' + SomeValue,3)

    select [Years] = datediff(year,0,ET),

    [Months] = datepart(month,ET-ST)-1,

    [Days] = datepart(day,ET-ST)-1,

    [JulianDays] = datediff(day,ST,ET),

    [Hours] = datepart(Hour,ET-ST),

    [Minutes] = datepart(Minute,ET-ST),

    [Seconds] = datepart(Second,ET-ST),

    [Milliseconds] = datepart(millisecond,ET-ST)

    from

    (

    select -- Test Data

    ST = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),

    ET = getdate()

    ) a

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

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