Getting only Month and Year from SQL DATE

Question

I need to access only Month.Year from Date field in SQL Server.

Answer 1

select SubString(convert(varchar, getdate(), 0), 1, 3) + ' ' + cast(year(GETDATE()) as varchar(4))

Answer 2

Some of the databases such as MS ACCESS or RODBC may not support the SQL SERVER functions, but for any database that has the FORMAT function you can simply do this:

SELECT FORMAT(<your-date-field>,"yyyy-MM") AS year-date FROM <your-table>

Answer 3

Sometimes you may want to consider a computed column for this.

ALTER TABLE dbo.ClubMetadata ADD JoinYear AS YEAR(JoinDate);
ALTER TABLE dbo.ClubMetadata ADD JoinMonth AS MONTH(JoinDate);

This can be useful for readability and also if you want to index the data since deterministic computed columns can be indexed.

Answer 4

Function "Format" is a convenient and flexible, but there is a performance concern, more details

If you are not very serious about formatting, grab year month by converting it as a string, the running time is acceptable as well

SELECT substring(<DateColumn> as varchar) ,0, 8 ) as YearMonth

Answer 5

For MySQL, this works pretty well:

DATE_FORMAT(<your-date>, '%Y-%m %M')

It goes into the SELECT statement. Here's the output:

2014-03 March

For more information about DATE_FORMAT, check out the documentation: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Answer 6

SELECT * FROM demo 
    WHERE attendance_year_month < SUBSTRING(CURRENT_TIMESTAMP,1,11) 
          AND
          attendance_year_month >= DATE_SUB(SUBSTRING(CURRENT_TIMESTAMP,1,11), INTERVAL 6 MONTH)

Answer 7

select CONCAT(MONTH(GETDATE()),'.',YEAR(GETDATE()))
Output: **5.2020**

select CONCAT(DATENAME(MONTH , GETDATE()),'.',YEAR(GETDATE()))
Output: **May.2020**

Answer 8

For reporting purposes I tend to use

CONCAT(YEAR([DateColumn]), RIGHT(CONCAT('00', MONTH([DateColumn])), 2))

You will lose the date type, since that will return (n)varchar. But with the leading zero this column is sortable (e.g. '202112' > '202102'). You could also add a seperator between year and month like

CONCAT(YEAR([DateColumn]), '-', RIGHT(CONCAT('00', MONTH([DateColumn])), 2))

returning somethink like '2020-08' (still sortable).

Answer 9

Another simple answer is to remove the number of day from the date

Here how to do it in BigQuery

SELECT DATE_ADD(<date_field>, INTERVAL 1 - extract(day from <date_field>) DAY) as year_month_date

An exemple :

SELECT DATE_ADD(date '2021-03-25', INTERVAL 1 - extract(day from date '2021-03-25') DAY) as year_month_date

This return 2021-03-01

Answer 10

Try SELECT CONCAT(month(datefield), '.', year(datefield)) FROM YOURTABLE;

Answer 11

I had a specific requirement to do something similar where it would show month-year which can be done by the following:

SELECT DATENAME(month, GETDATE()) + '-' + CAST(YEAR(GETDATE()) AS nvarchar) AS 'Month-Year'

In my particular case, I needed to have it down to the 3 letter month abreviation with a 2 digit year, looking something like this: SELECT LEFT(DATENAME(month, GETDATE()), 3) + '-' + CAST(RIGHT(YEAR(GETDATE()),2) AS nvarchar(2)) AS 'Month-Year'

Answer 12

Try this:

Portuguese

SELECT format(dateadd(month, 0, getdate()), 'MMMM', 'pt-pt') + ' ' + convert(varchar(10),year(getdate()),100)

Result: maio 2019

---

English

SELECT format(dateadd(month, 0, getdate()), 'MMMM', 'en-US') + ' ' + convert(varchar(10),year(getdate()),100)

Result: May 2019

If you want in another language, change 'pt-pt' or 'en-US' to any of these in link

Answer 13

CONCAT (datepart (yy,DATE), FORMAT (DATE,'MM')) 

gives you eg 201601 if you want a six digit result

Answer 14

select convert(varchar(11), transfer_date, 106) 

got me my desired result of date formatted as 07 Mar 2018

My column 'transfer_date' is a datetime type column and I am using SQL Server 2017 on azure

Answer 15

For result: "YYYY-MM"

SELECT cast(YEAR(<DateColumn>) as varchar) + '-' + cast(Month(<DateColumn>) as varchar)

Answer 16

Query :- Select datename(m,GETDATE())+'-'+cast(datepart(yyyy,GETDATE()) as varchar) as FieldName

Output :- January-2019

general datefield we can use

datename(m,<DateField>)+' '+cast(datepart(yyyy,<DateField>) as varchar) as FieldName

Answer 17

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-')

Output: Mar-2019

Answer 18

Get Month & Year From Date

DECLARE @lcMonth nvarchar(10)
DECLARE @lcYear nvarchar(10)

SET @lcYear=(SELECT  DATEPART(YEAR,@Date))
SET @lcMonth=(SELECT  DATEPART(MONTH,@Date))

Answer 19

SELECT convert(varchar(7), getdate(), 126) 

You might wanna check out this website: http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Answer 20

There are two SQL function to do it:

• DATEPART()
• YEAR() and MONTH().

Refer to the linked documentation for details.

Answer 21

My database doesn't support most of the functions above however I found that this works:

SELECT * FROM table WHERE SUBSTR(datetime_column, starting_position, number_of_strings)=required_year_and_month;

for example: SELECT SUBSTR(created, 1,7) FROM table;

returns the year and month in the format "yyyy-mm"

Answer 22

let's write it this way: YEAR(anySqlDate) and MONTH(anySqlDate). Try it with YEAR(GETDATE()) for example.

Answer 23

Try this

select to_char(DATEFIELD,'MON') from YOUR_TABLE

eg.

select to_char(sysdate, 'MON') from dual

Answer 24

This can be helpful as well.

SELECT YEAR(0), MONTH(0), DAY(0);

or

SELECT YEAR(getdate()), MONTH(getdate()), DAY(getdate());

or

SELECT YEAR(yourDateField), MONTH(yourDateField), DAY(yourDateField);

Answer 25

RIGHT(CONVERT(VARCHAR(10), reg_dte, 105), 7) 

Answer 26

datename(m,column)+' '+cast(datepart(yyyy,column) as varchar) as MonthYear

the output will look like: 'December 2013'

Answer 27

convert(varchar(7), <date_field>, 120)
because 120 results in 'yyyy-MM-dd' which is varchar(10)
using varchar(7) will display only year and month

example:
select convert(varchar(7), <date_field>, 120), COUNT(*)
from <some_table>
group by convert(varchar(7), <date_field>, 120)
order by 1

Answer 28

As well as the suggestions given already, there is one other possiblity I can infer from your question:
- You still want the result to be a date
- But you want to 'discard' the Days, Hours, etc
- Leaving a year/month only date field

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

This gets the number of whole months from a base date (0) and then adds them to that base date. Thus rounding Down to the month in which the date is in.

NOTE: In SQL Server 2008, You will still have the TIME attached as 00:00:00.000 This is not exactly the same as "removing" any notation of day and time altogether. Also the DAY set to the first. e.g. 2009-10-01 00:00:00.000

Answer 29

I am interpreting your question in two ways.

a) You only need Month & Year seperately in which case here is the answer

select 
        [YEAR] = YEAR(getdate())
        ,[YEAR] = DATEPART(YY,getdate())
        , [MONTH] = month(getdate())
        ,[MONTH] = DATEPART(mm,getdate())
        ,[MONTH NAME] = DATENAME(mm, getdate()) 

b)

You want to display from a given date say '2009-11-24 09:01:55.483' in MONTH.YEAR format. So the output should come as 11.2009 in this case.

If that is supposed to be the case then try this(among other alternatives)

select [Month.Year] = STUFF(CONVERT(varchar(10), GETDATE(),104),1,3,'')

Answer 30

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