Julian Date Conversion

[ibethea]

I have a date which is in the following format "2002105"
The first four characters are the year the next three characters are the number day of year. How would I convert this to a datetime format: Example

DateFunction('2000032') should return 2000-02-01
DateFunction('2002098') should return 2002-04-08

 

[bperry]

here's an example of something that seems to work for me. perhaps you can adapt to what you are doing?

It assumes that your initial string 2002032 is a varchar. If is is not, we'll have to adjust a little bit to account for the datatypes.

If there's a better way, soem one may post it for us.

-----------------
declare @mydate varchar(7)
set @mydate = '2002060'

declare @newdate datetime
set @newdate =
DATEADD(dd,CAST(SUBSTRING(@mydate,5,3) as integer) - 1,
CAST('01/01/' + Substring(@mydate,1,4)as datetime))
select @newdate
-------------------------
bp

 

[SQLBill]

Try this:

create procedure my_sp_truedate
@julian int
as
declare @year smalldatetime,
@days int
set @year = '1/1/' + left(@julian, 4)
set @days = right(@julian, 3)
select (@year + @days) - 1

then run

exec my_sp_truedate '2002103'

You have to have the -1 in the select to compensate for the @year being 1/1/2002. Since January 1 is julian 001, you would get the wrong date adding the julian to the @year and setting the @year to 1/0/2002 is an invalid date.

I tried the script and it worked for me.

-SQLBill

 

[bperry]

And one final variation! If you are on SQL2000, you might like to set it up as a UserDefinedFunction.

You might use the function something like this:

declare @mydate varchar(7)
set @mydate = '2002060'
Select dbo.GetFullDate(@myDate) as Newdate

(Notice the dbo. owner qualification is required.

And the function itself would be created like this:

CREATE FUNCTION GetFullDate (
@myDate varchar(7)
)
RETURNS datetime AS
BEGIN
declare @newdate datetime
set @newdate =
DATEADD(dd,CAST(SUBSTRING(@mydate,5,3) as integer) - 1,
CAST('01/01/' + Substring(@mydate,1,4)as datetime))
return @newdate
END
-- END OF FUNCTION

 

[foxdev]

My self-nomination for the doesn't he have anything better to do award:

Julian Date is not the number of days elasped in the year; it is the number of days elapsed since January 1, 4713 BC.

 

[bperry]

Yes, I believe foxdev is quite correct. I guess 'julian' has developed this second interpretation in the IT business. I know that as long as I have been programming in Cobol (which is a looong time), the number of days in year has always been referred to as the 'Julian date'. I wonder how that got started.

 

[SQLBill]

The term "Julian" date is used by the military and government to show how many days since that year. So, 2002103 is April 13th 2002 and sometimes it's just referenced as Julian date 103. I'm sure others use the term that way....my calendar has Julian dates below the 'normal' date.

-SQLBill

 

[foxdev]

http://aa.usno.navy.mil/data/docs/JulianDate.html

My "Julian Date" peeve is my only personality flaw.
Well, that and modesty.

 

[SQLBill]

ibethea,

So, did any of our suggestions solve your issue?

-SQLBill

 

[ibethea]

Thanks people.

I'm just back from vacation. This will generally work. I'll need to make some adjustments for leap year and some other formatting issues. However, I believe these solutions address the problem.