SQL DAYOFYEAR() Function - Find Number of Days Completed in Current Year

The SQL DAYOFYEAR() is a function, and returns a integer indicating day of the year of a given input date value.

The SQL DAYOFYEAR() function is supports only date or datetime based table columns or fields.

It can be used in SELECT statement as well in where clause.

You may also get the day of year by using the DATEPART built-in function with dayofyear (or dy or y) as the first input. In this case, instead of a text like in DATENAME, the DayOfYear return value will be an integer. The INT return type of the DATEPART() functions.

Syntax:

DATEPART(dayofyear , < GivenDate>)
 /* OR */
DATEPART(dy , < GivenDate>)
 /* OR */
DATEPART(y , < GivenDate>)

Example 1:

SELECT DATEPART (dayofyear , GetDate()) AS 'Day Of year'
GO

/* Result */
171
 
SELECT DATEPART (dy , '2019-06-20 12:30:00') AS 'Day Of year'
GO

/* Result */
171
 
SELECT DATEPART (y , '2019-06-20') AS 'Day Of year'
GO

/* Result */
171

Example 2:

SELECT GETDATE() 'Today', 
                  DATEPART(dayofyear,GETDATE()) 'Day of Year'
SELECT GETDATE() 'Today', DATEPART(dy,GETDATE()) 'Day of Year'
SELECT GETDATE() 'Today', DATEPART(y,GETDATE()) 'Day of Year'

Conclusion: Use the DATEPART method if you want the result to be in integers. Use the DATENAME method if you want the return value to be a character string.

Example 1: To get the first day of the current year in SQL Server, use the following code:

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

Example 2: Write a query to get the first day of the current year.

SELECT MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1);

Output:

Example 3:

Select DATEADD(yy, DATEDIFF(yy,0,GETDATE()),0): 

in this case we will extract the difference between the years and add.

select * 
from
where datefield>=
dateadd(year,-1,
DATEADD(month, DATEDIFF(month, 0, getdate()), 0)--gives first date of month
)

or from 2012: you can use DATEFROMPARTS as mentioned by Alex in comments

select DATEFROMPARTS(YEAR(GETDATE()) - 1, MONTH(GETDATE()), 1)

To get the first day of the next year:

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)

To get the last day of the next year:

SELECT DATEADD (dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) +2, 0))

The EOMONTH function is a built-in function added in SQL Server 2012 that calculates the first and last days of the month for a provided date, with an optional offset. This function allows you to travel ahead and backward by supplying a second argument (which is optional).

Syntax - EOMONTH Function in SQL

EOMONTH ( start_date , month_to_add );

Where,

• start_date: A date expression that specifies the date for which to return the last day of the month.
• month_to_add: An optional integer expression that specifies the number of months to add to start_date.

Example 1:

SELECT DATEADD(DD,-(DAY(GETDATE() -1)), GETDATE()) AS FirstDate
SELECT DATEADD(DD,-(DAY(GETDATE())), DATEADD(MM, 1, GETDATE())) AS LastDate

In the queries above, I used the DATEADD() function. More information on the function can be found here.

The above queries will return the first and last days, as well as the date, month, year, and time. For instance, suppose it's any day in the month of February 2021.

Output:

Example 2: However, we want the First and Last day (in words). So, simply embed the two above queries inside the DATENAME() function. Here it is.

SELECT DATENAME(WEEKDAY, DATEADD(DD,-(DAY(GETDATE() -1)), GETDATE())) AS FirstDay
SELECT DATENAME(WEEKDAY, DATEADD(DD,-(DAY(GETDATE())), DATEADD(MM, 1, GETDATE()))) AS LastDay

The output is Monday and Sunday, respectively.

Example 3: To calculate the first and last day of the month of a given date using EOMONTH:

DECLARE @Date1 datetime;
SET @Date1 = '04/27/2019';
SELECT DATEADD(DAY, 1, EOMONTH(@Date1, -1)) as First Day of the Month';
SELECT EOMONTH (@Date1) as 'Last Day of Month';

Output:

Approach 1: Using DATEPART Function

In SQL Server, we may use the DATEPART() method to get Day of Year from a Date. We can use the datepart argument of the DATEPART function to provide dayofyear, dy, or y, which will all return the same answer. The return type of the DATEPART() method is INT.

Example 1: Our database has a table named software_sale with data in the columns id, software, and sale_date.

Let's see how many software items were sold on each day of the year. We'll utilise this to figure out which days of the year (not the actual year) had the highest or lowest sales.

SELECT DATE_PART('doy',sale_date) AS  day_of_year,
 COUNT(id) as count FROM software_sale GROUP BY day_of_year;

Here’s the result of the query:

Output:

Discussion:

• To get the number of days in a year from a date/time/datetime/timestamp column, use the DATE_PART() function. There are two arguments to this function. The date component (or time part) you wish the function to return is the first argument. We're going to use the function 'doy,' which returns the current year's day. Other sections, such as day, year, month, hour, minute, week, and so on, can be used.
• The second input is the date/time/datetime/timestamp from which the date/time portion should be extracted. This could be the name of a date/time/datetime/timestamp column or an expression that returns a date/time/datetime/timestamp value. (In our example, it's a date data type column.)
• The integer number of the day of the year (from 1 to 365/366) is returned by the method DATE_PART() with the 'doy' identifier. The sale date's day of the year (from the sale date column) is now presented in a new column called day_of_year. The 15th of October was the 288th day in both 2019 and 2018; on that day, three sales were made in total spanning two years (2 in 2019, 1 in 2018).
• To count the amount of sales, we use the COUNT() aggregate function. This function only accepts one argument, which is the programme ID number in this case. In this query, we also require the GROUP BY clause to group records by day of the year. (The actual argument in our case is day_of_year, which is an alias for DATE_PART ('doy',sale date).)

Example 2: Day of Year from a given date

SELECT '2018-02-21' 'Date', DATEPART(dayofyear,'2018-02-21') 'Day of Year'
SELECT '2018-02-21' 'Date',DATEPART(dy,'2018-02-21') 'Day of Year'
SELECT '2018-02-21' 'Date',DATEPART(y,'2018-02-21') 'Day of Year'

Result:

Example 3: We can use DATEPART() function to get Day of Year from Date in Sql Server:

SELECT GETDATE() 'Today', 
                  DATEPART(dayofyear,GETDATE()) 'Day of Year'
SELECT GETDATE() 'Today', DATEPART(dy,GETDATE()) 'Day of Year'
SELECT GETDATE() 'Today', DATEPART(y,GETDATE()) 'Day of Year'

RESULT:

Approach 2: Using DATENAME Function

Example: In SQL Server, we can use the DATENAME() function to get Day of Year from a Date. We must specify the datepart argument of the DATENAME function as dayofyear, dy, or y to achieve the same result. NVARCHAR is the return type of the DATENAME() method.

SELECT GETDATE() 'Today', DATENAME(dayofyear,GETDATE()) 'Day of Year'
SELECT GETDATE() 'Today', DATENAME (dy,GETDATE()) 'Day of Year'
SELECT GETDATE() 'Today', DATENAME (y,GETDATE()) 'Day of Year'

Approach 3: EXTRACT() function.

This isn't the only way to find out what day it is. The EXTRACT() function can also be used.

The following is an example of the syntax:

EXTRACT(YEAR FROM date)

The syntax is simple to understand. A date literal or an expression that evaluates to a date value can be used as the date.

The EXTRACT() function returns a number that corresponds to the date's year.

Example 1: Here’s the query you would write with the EXTRACT() function:

SELECT EXTRACT('doy' FROM sale_date)
         AS  day_of_year,
       COUNT(id) as count
FROM software_sale
GROUP BY day_of_year;

DATE PART is equal to this function (). The only change is that between the date component identification and the date and time parameter, we use FROM instead of a comma. The end outcome is identical.

Example 2: The EXTRACT() function can be used to extract the year from it.

The following example demonstrates how to get the year from the date of July 22, 2018:

SELECT EXTRACT(YEAR FROM '2018-07-22')

Output:

Note: EXTRACT() is SQL standard.