Getting only Month and Year from SQL DATE

by

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

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. How to create a specific SQL Server stored procedure? [closed]
  3. Should I use != or for not equal in T-SQL?
  4. How to update two tables in one statement in SQL Server 2005?
  5. Create a date from day month and year with T-SQL
  6. How should I pass a table name into a stored proc?
  7. Creating an index on a table variable
  8. Where value in column containing comma delimited values
  9. What is SYSNAME data type in SQL Server?
  10. What is the difference between using a cross join and putting a comma between the two tables?
  11. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  12. Pivot using SQL Server 2000
  13. Equivalent of Oracle’s RowID in SQL Server
  14. Sequence vs identity
  15. How to update and order by using ms sql
  16. How to store image in SQL Server database tables column [duplicate]
  17. SQL “between” not inclusive
  18. Can I set ignore_dup_key on for a primary key?
  19. SQL Server replace, remove all after certain character
  20. How to find column names for all tables in all databases in SQL Server
  21. Inserting rows into a table with one IDENTITY column only [duplicate]
  22. SQL Row_Number() function in Where Clause
  23. When using GETDATE() in many places, is it better to use a variable?
  24. How do I compare 2 rows from the same table (SQL Server)?
  25. SQL Server SELECT INTO @variable?
  26. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  27. Select a column if other column is null
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment