Get month and year from a datetime in SQL Server 2005

Question

I need the month year from the datetime in SQL Server like  Jan 2008   I m grouping the query by month  year  I ve searched and found functions like datepart  convert  etc   but none of them seem useful for this  Am I missing something here  Is there a function for this

User · Answer

The following works perfectly  I just used it  try it out   date format date   Y- c

User · Answer

In SQL server 2012  below can be used  select FORMAT getdate     MMM yyyy    This gives exact  Jun 2016

User · Answer

select  datepart month getdate    -- integer  1 2 3      datepart year getdate    -- integer  datename month getdate    -- string   September

User · Answer

Yes  you can use datename month intime  to get the month in text

User · Answer

I had the same problem and after looking around I found this   SELECT DATENAME yyyy  date  AS year FROM Income GROUP BY DATENAME yyyy  date    It s working great

User · Answer

datename month  od SHIP DATE   as MONTH    Answer   MONTH  January January September October December October September

User · Answer

returns the full month name  -  full year e g  March-2017  CONCAT DATENAME mm  GetDate      -   DATEPART yy  GetDate

User · Answer

Use   select datepart mm getdate     --to get month value select datename mm getdate     --to get name of month

User · Answer

The question is about SQL Server 2005  many of the answers here are for later version SQL Server   select convert  varchar 7   getdate   20  --Typical output 2015-04   SQL Server 2005 does not have date function which was introduced in SQL Server 2008

User · Answer

How about this   Select DateName  Month  getDate             DateName  Year  getDate

User · Answer

If you mean you want them back as a string  in that format   SELECT    CONVERT CHAR 4   date of birth  100    CONVERT CHAR 4   date of birth  120   FROM customers   Here are the other format options

User · Answer

---Lalmuni Demos--- create table Users   userid int date of birth date   ---insert values--- insert into Users values 4  9 10 1991    select DATEDIFF year date of birth  getdate    -  CASE WHEN  DATEADD year  DATEDIFF year date of birth  getdate    date of birth    gt  getdate   THEN 1 ELSE 0 END  as Years   MONTH getdate   -  DATEADD year  DATEDIFF year  date of birth  getdate     date of birth    - 1 as Months   DAY getdate   -  DATEADD year  DATEDIFF year date of birth  getdate     date of birth    - 1 as Days  from users

User · Answer

the best way to do that is with    dateadd month datediff month 0  your date   0    it will keep your datetime type

User · Answer

Converting the date to the first of the month allows you to Group By and Order By a single attribute  and it s faster in my experience   declare  mytable table mydate datetime  declare  date datetime set  date    19000101  while  date  lt  getdate   begin     insert into  mytable values  date      set  date   dateadd day 1  date  end  select count    total records from  mytable  select dateadd month datediff month 0 mydate  0  first of the month  count    cnt from  mytable group by dateadd month datediff month 0 mydate  0

User · Answer

Funny  I was just playing around writing this same query out in SQL Server and then LINQ   SELECT      DATENAME mm  article Created  AS Month       DATENAME yyyy  article Created  AS Year       COUNT    AS Total  FROM Articles AS article  GROUP BY      DATENAME mm  article Created        DATENAME yyyy  article Created   ORDER BY Month  Year DESC   It produces the following ouput  example    Month   Year   Total  January   2009   2

User · Answer

Month Created          Year Created    AS Date

User · Answer

cast cast sq QuotaDate as date  as varchar 7     gives  2006-04  format

User · Answer

It s work great   DECLARE  pYear VARCHAR 4   DECLARE  pMonth VARCHAR 2   DECLARE  pDay VARCHAR 2   SET  pYear    RIGHT CONVERT CHAR 10   GETDATE    101   4   SET  pMonth   LEFT CONVERT CHAR 10   GETDATE    101   2   SET  pDay     SUBSTRING CONVERT CHAR 10   GETDATE    101   4 2   SELECT  pYear  pMonth  pDay

User · Answer

Beginning with SQL Server 2012  you can use   SELECT FORMAT  date   yyyyMM

User · Answer

That format doesn t exist  You need to do a combination of two things   select convert varchar 4  getdate   100     convert varchar 4  year getdate

[sql] Get month and year from a datetime in SQL Server 2005

Examples related to sql

Examples related to sql-server