SQL find out how many days into year date is

Question

I want to work out an annual figure as a proportion of the year based on the date - so if 400 is the annual figure, I want to divide this by 365 then multiply the result by however many days is equivalent to today's date (e.g. (400/365)*160)).

Is there a way of doing this with a SQL server select statement to avoid manually entering the 160 figure?

The 400 figure is coming from a standard field named HES.

Thanks.

Were you looking for the difference in days between 01/01/2015 and today's date? `SELECT DATEDIFF(DAY, '01/01/15', GETDATE())`? — Anthony Forloney, Jun 09 '15 at 13:50
Of course that will malfunction as soon as 2016 rolls around. :) See the answer to [this question](http://stackoverflow.com/questions/13437362/how-to-get-first-and-last-date-of-current-year) for a way to get the first day of the current year. — Joe Farrell, Jun 09 '15 at 13:53
Assuming that you don't call this code at exactly midnight, does the day we're currently in count or not (i.e. is the answer throughout January 1st "1" or "0")? — Damien_The_Unbeliever, Jun 09 '15 at 13:55

score 5 · Accepted Answer · edited Jun 09 '15 at 14:06

5

You can use datepart(dayofyear, getdate()) - will return a number representing today's day of the year. See MSDN DatePart

edited Jun 09 '15 at 14:06

Gordon Linoff

1,242,037
58
646
786

answered Jun 09 '15 at 13:52

PaulMJax

216
2
6

1

That looks like MySQL. – Joe Farrell Jun 09 '15 at 13:52
Need to change curdate() to getdate() for this to work in sql server. – Sean Lange Jun 09 '15 at 13:54
1

You will need dayofyear instead of dayoftheyear. Other than that this looks like a great solution, so I removed mine that wasn't as elegant. :) – Martin Noreke Jun 09 '15 at 13:54

score 1 · Answer 2 · answered Jun 09 '15 at 13:55

1

Since this is sql server and the other answer is using mysql I will post the sql server version.

select DATEPART(dayofyear, getdate())

answered Jun 09 '15 at 13:55

Sean Lange

33,028
3
25
40

score 0 · Answer 3 · answered Jun 09 '15 at 14:03

For your calculation, you might want to take leap years into account. SQL Server has a convenient datepart(dayofyear, . . ) functionality. The complete solution would look like:

select datepart(dayofyear, dateadd(day, -1, cast(cast(year(getdate() + 1) as varchar(255)) + '0101' as date))) as daysinyear,
       datepart(dayofyear, getdate()) as currentday,
       datepart(dayofyear, getdate()) * 1.0 / datepart(dayofyear, dateadd(day, -1, cast(cast(year(getdate() + 1) as varchar(255)) + '0101' as date)))as daysinyear

Note that SQL Server does integer division, so to get a fraction, you need to convert to a decimal representation of some sort (* 1.0 is just a simple way of doing this).

SQL find out how many days into year date is

3 Answers3