Retrieving only month and year from SQL DATE

Question

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

Answer 1

Like the suggestions already mentioned, there is another possibility I can make from your question:
- You still want the result to be a date
- But you want to “refuse” days, hours, etc.
- Leave the date field for only a month / month

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

This is the number of whole months from the base date (0), and then adds them to this base date. Thus, rounding to the month in which the date is located.

NOTE. In SQL Server 2008, you will still have TIME set to 00: 00: 00.000. This is not exactly the same as deleting any day and time symbols in general. Also DAY is set to the first. e.g. 2009-10-01 00: 00: 00.000

Answer 2

 select month(dateField), year(dateField) 

Answer 3

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

Answer 4

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

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

Answer 5

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

the output would look like this: 'december 2013'

Answer 6

There are two SQL functions for this:

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

See related documentation for more details.

Answer 7

write like this: YEAR(anySqlDate) and MONTH(anySqlDate) . Try, for example, using YEAR(GETDATE()) .

Answer 8

It may also be helpful.

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

or

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

or

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

Answer 9

 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 10

I interpret your question in two ways.

a) You only need a month and a year separately, 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 the specified date, say '2009-11-24 09:01:55.483' in MONTH.YEAR format. Thus, the output should look like 11.2009 in this case.

If it should be, try this (among other alternatives)

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

Answer 11

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

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

Answer 12

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

Answer 13

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

for example, 201601, if you want to get a six-digit result

Answer 14

try it

 select to_char(DATEFIELD,'MON') from YOUR_TABLE 

eg.

 select to_char(sysdate, 'MON') from dual 

Answer 15

Try this:

Portuguese

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

Result: Mayo 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, replace " pt-pt " or " en-US " with any of these in the link

Answer 16

My database does not support most of the above functions, 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 17

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

got me the desired date result in the format of March 07, 2018

My "Transfer_date" column is a datetime column and I am using SQL Server 2017 in Azure

Answer 18

For the result: "YYYY-MM"

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

Answer 19

Get month and year from date

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

Answer 20

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

Output: - January-2019

general date field we can use

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

Answer 21

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

Exit: March 2019