Getting only Month and Year from SQL DATE

Question

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

Thanks in advance.

Answer 1

select month(dateField), year(dateField)

Answer 2

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

Answer 3

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

Answer 4

There are two SQL function to do it:

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

See MSDN docs for details.

Answer 5

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.

Answer 6

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