• Register
0 votes
53 views

Get month and year from the date in SQL

I need to access the only month.year from the date field in SQL Server. Any idea?
1.9k points

Please log in or register to answer this question.

1 Answer

0 votes

Get month and year from the date in SQL.

SQL includes a series of functions that help work with dates and times. A task when working with dates is to extract different parts of the date. You want the month and the year. Other times you may wish to the day of the week. There are many ways to do this in SQL Server.

The next functions allow you to return the day, month, and year from SQL Server data.

•DAY(), MONTH(), and YEAR()
•DATEPART()
•DATENAME()
•FORMAT()

I will give you more details about these functions.

The FORMAT() functions

You can use the FORMAT() function to return the same values as with the DATENAME() function, and more. It is a versatile function and allows to format date/time and numeric values as strings. The function returns a nvarchar value or null (depending on the input value). The string length depends on the specified format.

FORMAT() provides an option for how the datepart is presented. You can specify to display it as a single character, two characters, three, four, and five characters. Here is an example.

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    FORMAT(@date, 'd ') AS d,
    FORMAT(@date, 'dd') AS dd,
    FORMAT(@date, 'ddd') AS ddd,
    FORMAT(@date, 'dddd') AS dddd;

Day:

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    FORMAT(@date, 'd ') AS d,
    FORMAT(@date, 'dd') AS dd,
    FORMAT(@date, 'ddd') AS ddd,
    FORMAT(@date, 'dddd') AS dddd;

Month

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    FORMAT(@date, 'M ') AS M,
    FORMAT(@date, 'MM') AS MM,
    FORMAT(@date, 'MMM') AS MMM,
    FORMAT(@date, 'MMMMM') AS MMMM;

Year

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT     
    FORMAT(@date, 'y ') AS y,
    FORMAT(@date, 'yy') AS yy,
    FORMAT(@date, 'yyy') AS yyy,
    FORMAT(@date, 'yyyy') AS yyyy,
    FORMAT(@date, 'yyyyy') AS yyyyy;

When using the single option as the date specifier, you need to add a space if you want datepart returned.

DECLARE @date datetime2 = '2008-06-02 08:24:14.3112042';
SELECT 
    FORMAT(@date, 'd ') AS 'Space',
    FORMAT(@date, 'd') AS 'No Space',
    FORMAT(@date, 'M ') AS 'Space',
    FORMAT(@date, 'M') AS 'No Space',
    FORMAT(@date, 'y ') AS 'Space',
    FORMAT(@date, 'y') AS 'No Space';

There are a few more solutions:

SELECT
   DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field]
FROM
   <your_table>

The code gets

the number of months from the database date (0).

Here is another shortcode that solves your problem:

select month(dateField), year(dateField)

This code works too:

SELECT DATEPART(yy, DateVal)
SELECT DATEPART(MM, DateVal)
SELECT DATENAME(MM, DateVal)

As you can see, there are good solutions to your problem. You are spoiled for choice.

3.4k points

Related questions

0 votes
1 answer 23 views
23 views
Problem: I want to get month and year from date in SQL SERVER. I do not know how to do that. If someone could help me, it would be appreciated. Thanks
asked Oct 19, 2020 Code Learner 10.5k points
0 votes
1 answer 47 views
47 views
Problem: How can I get the name of the month from the month number? For example, if I have 3, I want to returnmarch date.tm_month() How do I get the string march?
asked Dec 1, 2020 sasha 16.2k points
0 votes
1 answer 49 views
49 views
Problem: I am struggling to show the next leap year. I have my initial leap year. What and where should I code? Conclusion: 2016 is a leap year. 2020 is a leap year following 2016. import java.util.Scanner; public class LabProgram { public static void main(String[] args) { Scanner ... .println(inputYear + " is a leap year."); } else { System.out.println(inputYear + " is not a leap year."); } }}
asked Dec 20, 2020 sasha 16.2k points
0 votes
1 answer 12 views
12 views
Problem: Any idea about it ,How to fix it. Thanks in advance
asked Mar 19 PkGuy 25.9k points
0 votes
1 answer 23 views
23 views
Problem: why i got this error: how to add one month to date in php? can anyone help me to solve this, thanks in advance.
asked Mar 12 jasimchaudary 1.5k points
0 votes
1 answer 12 views
12 views
Problem: Try to discover this : A 60-day note is signed on february 15 (and it's not leap year). the due date of the note is:
asked Jun 12 Chi Omega 159k points
0 votes
1 answer 17 views
17 views
Problem: How to write Python program to check leap year using function?
asked May 24 Irenka Kiri 85.5k points
0 votes
1 answer 28 views
28 views
Problem: Can someone explain to me as to why am I getting the above message:what is the date 3 months from now?
asked Apr 17 ashik 14.6k points
0 votes
1 answer 14 views
14 views
Problem: Cannot find any solution, help needed : How to get month and year from date in sql?
asked May 31 Chi Omega 159k points
0 votes
1 answer 7 views
7 views
Problem: How to get the last date of the month in SQL?
asked Apr 14 jamuna1 30.2k points