Function to convert date from text format YYYYDDD where DDD = number of days in the year

i should say, where DDD = day of year !

replace(convert(varchar(10), convert(datetime, '2007305', 112), 106), ' ', '-')

sorry, posted to fast...

create function dbo.CnvDateYYYYDDD(@v varchar(7))
returns varchar(11)
as
begin
 declare @d datetime
 declare @res varchar(11)
 set @d = dateadd(day, cast(right(@v, 3) as int) -1, convert(datetime, left(@v,4) + '01-01', 120))
 set @v = replace(convert(varchar(11), @v, 106), ' ', '-')
 return @v
end

select dbo.CnvDateYYYYDDD('2007305')

Server: Msg 241, Level 16, State 1, Procedure CnvDateYYYYDDD, Line 7
Syntax error converting datetime from character string.

this seems to work (several edits!)..

create function dbo.CnvDateYYYYDDD(@v varchar(7))
returns varchar(11)
as
begin
 declare @d datetime
 declare @res varchar(11)
 set @d = dateadd(day, cast(right(@v, 3) as int)-1, convert(datetime, left(@v,4) + '-01-01', 120))
 set @res = replace(convert(varchar(11), @d, 106), ' ', '-')
 return @res
end

Correction also, select dbo.CnvDateYYYYDDD('2007305') correctly returns 1st-Nov-2007

Thanks for helping get the solution. Copy-paste the above corrected function for the points (just makes PAQ simpler)

Functions should be written *for efficiency* -- no extraneous variables, code, etc. -- rather than to show step-by-step logic.  For example:

CREATE FUNCTION dbo.CnvDateYYYYDDD(
    @yyyyddd varchar(7)
)
RETURNS varchar(11)
AS
BEGIN
RETURN REPLACE(CONVERT(VARCHAR(11), 
    DATEADD(DAY, CAST(RIGHT(@yyyyddd, 3) AS INT) - 1, LEFT(@yyyyddd, 4) + '0101')
    , 106), ' ', '-')
END

Select allOpen in new window

Scott, thanks for the suggestion, i agree efficiency is important. Though in many cases human efficiency is more valuable than machine efficiency, so clearer coding may be preferred.

>>  Though in many cases human efficiency is more valuable than machine efficiency <<

I wouldn't recommend that for functions -- you could end up noticeably slowing down your system.  When you start applying a function to every row of a table, efficiency can make a *big* difference, especially when the table goes from 100 rows to 100,000 or 1M rows for some reason.

I do agree with ScottPletcher.
you should consider writing the function with the most effective code, and comment with the more human-readable version...

Ok thanks, fair point. Follow-up 1 coming up, as i need the output as Datetime..

Replacement solution for DateTime output..
https://www.experts-exchange.com/questions/22974218/Part-II-Function-to-convert-date-from-text-format-YYYYDDD-where-DDD-day-of-year.html