Opposite of dayofyear

• ikorenbl

  SSCommitted

  Points: 1750

  More actions

  September 17, 2003 at 2:36 pm

  #84437

  I have a problem of converting character column that contains a year and a number of days into the datetime format.

  Sample data - 2003260

  The following statement produces 260:

  select datepart(dayofyear, '09/17/2003')

  I need the opposite function to interpret 2003260 into 09/17/2003.

  Any ideas?

  Thanks

• Greg Larsen

  SSC-Insane

  Points: 20966

  More actions

  September 17, 2003 at 2:41 pm

  #474749

  Try something like this:

  declare @jdate char(7)

  set @jdate = '2003260'

  select convert(char(10),

  dateadd(day,cast(substring(@jdate,5,7) as int)-1,cast(substring(@jdate,1,4) + '-01-01' as datetime))

  , 101)

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP

• abnormalized

  SSC Rookie

  Points: 25

  More actions

  September 18, 2003 at 9:00 am

  #474887

  SELECT DATEADD(d,RIGHT(@d,3)-1,LEFT(@jd,4))

• ikorenbl

  SSCommitted

  Points: 1750

  More actions

  September 18, 2003 at 11:37 am

  #474920

  Thanks guys,

  Both solutions seem to work.