Getting only Month and Year from SQL DATE

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

select month(dateField), year(dateField)

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

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,'')

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

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

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

the output will look like: 'December 2013'

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

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

Try this

select to_char(DATEFIELD,'MON') from YOUR_TABLE

eg.

select to_char(sysdate, 'MON') from dual