Post reply

Julian date YYYYJJJ into YYYYMMDD

  • March 22, 2010 at 8:21 am

    #146103

    Hello, I have a column of julian dates YYYYJJJ that I need to compare to a column of YYYYMMDD to find matches to update a field. Anyone know how I can do this?

    I am trying to

    update table a

    Set a.field1 = b.field2

    where Jdate = Sdate

    JDate = YYYYJJJ

    Sdate = YYYYMMDD

    Thank you for any advice.

    PN

  • March 22, 2010 at 8:32 am

    #1137615

    once you've converted to datetime, the comparison should be easy;

    here's how to convert your julian date; the other appears to already be datetime....if it's not, explicitly convert it to datetime.

    update table a

    Set a.field1 = b.field2

    where dateadd(day,(jdate % 1000) -1,convert(datetime,'01/01/' + LEFT(jdate,4) ))= convert(datetime,Sdate)

    --results:

    2009-09-09 00:00:00.000

    the code:

    DECLARE @sdate int

    SET @sdate = 2009252 --your date said YYYYJJJ, so that would be

    select dateadd(day,(@sdate % 1000) -1,convert(datetime,'01/01/' + LEFT(@sdate,4) ))

    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!

  • March 22, 2010 at 8:44 am

    #1137623

    Awesome...this was the key I was looking for. Many thankx.

    I adjusted it with convert to get into YYYYMMDD:

    Select convert(varchar(8),dateadd(day,(substring(HBEFFDTE,2,7) % 1000) -1,convert(datetime,'01/01/' + LEFT(substring(HBEFFDTE,2,7),4) )),112)

    Again thank you.

  • March 22, 2010 at 8:48 am

    #1137624

    data that is in YYYYMMDD format will natively change to datetime with the code i added...no need to chop it up into pieces; try convert(datetime,sdate) and it should work fine.

    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!

  • March 22, 2010 at 8:53 am

    #1137628

    You are on fire today...2 for 2. That worked.

    Is there better performance with the second way?

  • March 22, 2010 at 9:00 am

    #1137632

    i'd have to test it, but by avoiding some string manipulations, it might save a microsecond or two on a big batch; i think it's just shorter and easier on the eyes.

    YYMMDD is the 'universal' datetime format, so it converts easily.

    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!

  • March 22, 2010 at 10:06 am

    #1137666

    Thank you.

    Ok to close. Answer is on Target.

  • March 22, 2010 at 11:40 am

    #1137718

    Here is another option:

    declare @JDate int;

    set @JDate = 2010032;

    select dateadd(dd, ((@JDate % 1000) - 1), DATEADD(yy, ((@JDate / 1000) - 1900), CAST('1900-01-01' as datetime)))

Viewing 8 posts - 1 through 7 (of 7 total)

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