Getting Only Month and Year from SQL Date

Getting only Month and Year from SQL DATE

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, ), 0) AS [year_month_date_field]
FROM
   

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

How to return only the year and month from a SQL Server DateTime datatype

SELECT FORMAT ( GETDATE() , 'yyyy-MM' )

Ok so if you have a Table named MyTable with a Column named DateCol of Type DateTime you can use the query below:

SELECT FORMAT ( DateCol , 'yyyy-MM' ) FROM MyTable 

Get only month and year in SQL Server

As an alternative approach, you could go for:

RIGHT(REPLACE(CONVERT(varchar(8),DateColumn,3),'/',''),4)

How to get month and year from date in SQL

This may help:

SQL Server:

SELECT FORMAT (GETDATE(), 'MMM yyyy')  -- Jul 2019
SELECT FORMAT (GETDATE(), 'MMMM yyyy') -- July 2019
SELECT RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8) -- Jul 2019

For more details: https://www.tutorialgateway.org/sql-date-format/

MySQL:

SELECT DATE_FORMAT("20150102", "%M %Y");  -- January 2015
SELECT DATE_FORMAT("20150102", "%b %Y"); -- Jan 2015
SELECT YEAR(date) AS 'year', MONTH(date) AS 'month'

For more details: http://www.sqlines.com/mysql-to-oracle/date_format

get only month and year from date

You can get a numeric representation in a string by using:

select extract(year from dateper) as yyyy, extract(month from dateper) as mm
from calcul
group by yyyy, mm;

Or:

select to_char(dateper, 'YYYY-MM') 
from calcul
group by to_char(dateper, 'YYYY-MM') ;

Getting only day and month from a date field

If your column is of type DATE then it doesn't have a format.

If I understand you right, then you want to view the mon-dd part only, so you need to convert it with TO_CHAR function,

i.e.:

select to_char(your_date_column, 'mon-dd') from your_table

extract year and month from date field mysql

SELECT CONCAT(year(task_completion), '-' ,month(task_completion)) FROM task

Extract month name and year from timestamp to same column

Example for you:

select 
    TO_CHAR(created_at, 'Month') AS "Month", 
    TO_CHAR(created_at, 'YYYY') AS "Year", 
    trim(TO_CHAR(created_at, 'Month')) || ', ' || trim(TO_CHAR(created_at, 'yyyy')) as mydate   
from acc_facts

Result data:

January     2022    January, 2022
January     2022    January, 2022
May         2022    May, 2022
March       2022    March, 2022

Get month and year from a datetime in SQL Server 2005

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

---

Related Topics