Getting only Month and Year from SQL DATE

Question

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

User · Answer

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

User · Answer

Try this  select to char DATEFIELD  MON   from YOUR TABLE   eg   select to char sysdate   MON   from dual

User · Answer

CONCAT  datepart  yy DATE   FORMAT  DATE  MM       gives you eg 201601 if you want a six digit result

User · Answer

I had a specific requirement to do something similar where it would show month-year which can be done by the following    SELECT DATENAME month  GETDATE       -     CAST YEAR GETDATE    AS nvarchar  AS  Month-Year   In my particular case  I needed to have it down to the 3 letter month abreviation with a 2 digit year  looking something like this   SELECT LEFT DATENAME month  GETDATE     3     -     CAST RIGHT YEAR GETDATE    2  AS nvarchar 2   AS  Month-Year

User · Answer

SELECT convert varchar 7   getdate    126     You might wanna check out this website  http   anubhavg wordpress com 2009 06 11 how-to-format-datetime-date-in-sql-server-2005

User · Answer

convert varchar 7    lt date field gt   120  because 120 results in  yyyy-MM-dd  which is varchar 10  using varchar 7  will display only year and month  example  select convert varchar 7    lt date field gt   120   COUNT    from  lt some table gt  group by convert varchar 7    lt date field gt   120  order by 1

User · Answer

This can be helpful as well   SELECT YEAR 0   MONTH 0   DAY 0     or   SELECT YEAR getdate     MONTH getdate     DAY getdate       or   SELECT YEAR yourDateField   MONTH yourDateField   DAY yourDateField

User · Answer

Query  -  Select datename m GETDATE     -  cast datepart yyyy GETDATE    as varchar  as FieldName  Output  - January-2019  general datefield we can use  datename m  lt DateField gt       cast datepart yyyy  lt DateField gt   as varchar  as FieldName

User · Answer

My database doesn t support most of the functions above however I found that this works   SELECT   FROM table WHERE SUBSTR datetime column  starting position  number of strings  required year and month    for example  SELECT SUBSTR created  1 7  FROM table   returns the year and month in the format  yyyy-mm

User · Answer

SELECT DATEPART yy  DateVal  SELECT DATEPART MM  DateVal  SELECT DATENAME MM  DateVal

User · Answer

Some of the databases such as MS ACCESS or RODBC may not support the SQL SERVER functions  but for any database that has the FORMAT function you can simply do this   SELECT FORMAT  lt your-date-field gt   YYYY-MM   AS year-date FROM  lt your-table gt

User · Answer

select CONCAT MONTH GETDATE        YEAR GETDATE      Output  5 2020  select CONCAT DATENAME MONTH   GETDATE        YEAR GETDATE      Output  May 2020

User · Answer

For result   YYYY-MM    SELECT cast YEAR  lt DateColumn gt   as varchar     -    cast Month  lt DateColumn gt   as varchar

User · Answer

datename m column      cast datepart yyyy column  as varchar  as MonthYear   the output will look like   December 2013

User · Answer

There are two SQL function to do it    DATEPART   YEAR   and MONTH      Refer to the linked documentation for details

User · Answer

let s write it this way  YEAR anySqlDate  and MONTH anySqlDate   Try it with YEAR GETDATE    for example

User · Answer

Try SELECT CONCAT month datefield        year datefield   FROM YOURTABLE

User · Answer

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   lt dateField gt    0  AS  year month date field  FROM     lt your table gt    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

User · Answer

SELECT REPLACE RIGHT CONVERT VARCHAR 11   GETDATE    106   8         -     Output  Mar-2019

User · Answer

select month dateField   year dateField

User · Answer

select convert varchar 11   transfer date  106     got me my desired result of date formatted as 07 Mar 2018  My column  transfer date  is a datetime type column and I am using SQL Server 2017 on azure

User · Answer

Try this   Portuguese  SELECT format dateadd month  0  getdate      MMMM    pt-pt           convert varchar 10  year getdate    100    Result   maio 2019    English  SELECT format dateadd month  0  getdate      MMMM    en-US           convert varchar 10  year getdate    100    Result   May 2019  If you want in another language  change  pt-pt  or  en-US  to any of these in link

User · Answer

RIGHT CONVERT VARCHAR 10   reg dte  105   7

User · Answer

Get Month  amp  Year From Date  DECLARE  lcMonth nvarchar 10  DECLARE  lcYear nvarchar 10   SET  lcYear  SELECT  DATEPART YEAR  Date   SET  lcMonth  SELECT  DATEPART MONTH  Date

[sql] Getting only Month and Year from SQL DATE

Examples related to sql

Examples related to sql-server

Examples related to tsql