SQL: How to get the date yyyy/mm/dd based on the year and day number?

Question

I have the following string 2015089 or 2016075, for example.

I need to get the result in yyyy/mm/dd format based on the given input.

So, based on 2015089, I get, 2015/mm/dd. dd is a 89th day of 2015 and mm is a month that has 89th day.

How can I do something like that?

http://stackoverflow.com/a/207232/507793 – Matthew Apr 24 '17 at 21:14 — Matthew, Apr 24 '17 at 21:14
Is Jan 1 represented as "1" or "0"? – Gordon Linoff Apr 24 '17 at 21:14 — Gordon Linoff, Apr 24 '17 at 21:14

Gordon Linoff · Answer 1 · 2017-04-24T21:15:03.123

4

I think the simplest way is to convert to a date using dateadd():

select dateadd(day, right(str, 3) - 1, datefromparts(left(str, 4) + 0, 1, 1) )

That is, add one less than the number of days to the beginning of the year. This assumes that Jan 1 is represented as "1" and not "0".

You can then format the date however you like.

In pre-SQL Server 2012, you can do:

select dateadd(day, right(str, 3) - 1, cast(left(str, 4) + '0101' as date))

edited Apr 24 '17 at 21:15

answered Apr 24 '17 at 21:13

Gordon Linoff

This is a nice answer. But as a disclaimer, it's valid for SQL Server 2012+. – Lamak Apr 24 '17 at 21:14

1 Answers1