Julian date YYYYJJJ into YYYYMMDD

Question

Julian date YYYYJJJ into YYYYMMDD

patrickn

SSC Eights!

Points: 905
More actions
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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

patrickn SSC Eights! Points: 905 More actions · Answer 2

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.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

patrickn SSC Eights! Points: 905 More actions · Answer 4

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

Is there better performance with the second way?

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!

patrickn SSC Eights! Points: 905 More actions · Answer 6

patrickn

SSC Eights!

Points: 905

March 22, 2010 at 10:06 am

#1137666

Thank you.

Ok to close. Answer is on Target.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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)))