Getting only Month and Year from SQL DATE

Question

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

Answer 1

If you want the result to be a date. But you want to 'discard' the Days, Hours, etc. Leaving a year/month only date field. Then, use the below code:

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

FROM <your_table>

The above code will give you 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 have the TIME attached as 00:00:00.000. But, this is not exactly the same as "removing" any notation of day and time altogether. 

Also, the DAY set should be the first. e.g. 2009-10-01 00:00:00.000