Getting only Month and Year from SQL DATE

Posted By: Anonymous

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

Solution

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

Answered By: Anonymous

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.