Making a few assumptions… That looks like you have a YYYYJJJ date, where the rightmost 3 characters are the Julian day of year. In SQL Server, there are no built-in Julian date functions, so you need to massage it via code. One possibility:
create function fyyyyjjj2datestring (
declare @year varchar(4)
declare @jjj varchar(3)
declare @date datetime
declare @outstring varchar(32)
set @year = left(@juliandate, 4) — extract the year part for later
set @jjj = right(@juliandate, 3) — extract the julian day part for later
set @date = convert(datetime, ’01-01-’ + @year) — generate the date value for 1/1/yyyy
set @date = dateadd(d, convert(int, @jjj)-1, @date) — offset to the julian day (note the “-1″!)
set @outstring = replace(convert(varchar(32), @date, 106), ‘ ‘, ‘-’) — convert to a formatted date result
– note that there is no defined format with “dd-MON-yyyy”, so I used style 106
– which is “dd MON yyyy” and then replaced the spaces with hyphens.
As expected, this function returns the string “03-MAR-2009″ for “2009062″ as the input.
If your input is numeric instead of a string, then the 1st 2 “sets” would be:
set @year = convert(varchar(4), @juliandate / 1000)
set @jjj = @juliandate mod 1000 — also, declare @jjj as int and eliminate the convert in the dateadd
Note the “-1″ in the dateadd expression. Since you cannot define a date as “january 0, yyyy”, the JJJ offset needs to be bumped down (2009001 = 1/1/2009 = 01-01-2009 + 1 – 1).