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.

Answer 1

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

Answer 2

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

select DATEPART(dayofyear, getdate())

Answer 3

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).