Converting YYYYDDD to datetime

[jpicks]

using sql server 2000, sp3

Hi all. I'm hoping someone out there has had to solve this problem before. I am attempting to convert dates that are formatted as YYYYDDD to datetime.

YYYY = Year
DDD = Day of year

I am planning on writing a udf to handle this conversion, but have been struggling with converting the day of year value into a month and day value.

Any help or suggestions would be greatly appreciated!

Thanks,

Jim

 

[mrdenny]

Figure out the correct year, and stick it in a variable as Jan 1. Then day the Day of year and add that number of days using dateadd.

Declare @Year int
Declare @DayOfYear int
Declare @InputData varchar(7)
declare @TheDate datetime
set @Year = left(@Inputdate, 4)
set @DayOfYear = right(@InputDate, 3)
set @TheDate = convert(datetime, '1/1/' + convert(varchar(4), @Year))
set @TheDate = dateadd(dd, @DayOfYear, @TheDate)
select @TheDate

You'll need to turn that into a udf, but it should do the trick.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jpicks]

Thanks for the help. Your logic on this works great!

FYI - I had to change one line to calc the date correctly.

Declare @Year int
Declare @DayOfYear int
Declare @InputData varchar(7)
declare @TheDate datetime
set @Year = left(@Inputdate, 4)
set @DayOfYear = right(@InputDate, 3)
set @TheDate = convert(datetime, '1/1/' + convert(varchar(4), @Year))
set @TheDate = dateadd(dd, @DayOfYear-1, @TheDate)
select @TheDate

 

[mrdenny]

very true. Didn't catch that.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jpicks]

In case anyone else out there is struggling with this problem, here's a copy of the udf that I built to do this date conversion:


CREATE FUNCTION [dbo].[convertYYYYDDDtoDatetime](@InputDate varchar(7))
RETURNS datetime AS
BEGIN
Declare @Year int
Declare @DayOfYear int
Declare @TheDate datetime

Set @Year = LEFT(@Inputdate, 4)
Set @DayOfYear = RIGHT(@InputDate, 3)
Set @TheDate = CONVERT(datetime, '1/1/' + convert(Varchar(4), @Year))
Set @TheDate = DATEADD(dd, @DayOfYear-1, @TheDate)

Declare @GregorianDate datetime

IF @InputDate = '0000000'
Set @GregorianDate = NULL
ELSE
Set @GregorianDate = @TheDate

Return @GregorianDate
END