SQL: Trying to extract year and month from a date

Question

I'm trying to extract the year and month from a stored date in SQL. I've seen lots of select examples, but I'm trying to use it in the where clause.

The date is stored as DD-mon-YYYY.

I have:

where s.stored_date=to_date('Dec 2015','mon-YYYY');

Possible duplicate of [Select from table by knowing only date without time (ORACLE)](http://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle) — Phil, Oct 06 '15 at 02:35
do you mean you want to filter some rows by year and month only? have you tried EXTRACT function? — 1010, Oct 06 '15 at 02:42

score 0 · Answer 1 · answered Oct 06 '15 at 02:41

In MySQL you can use internal functions str_to_date() and date_format() like this:

where s.stored_date = date_format(str_to_date('Dec 2015', '%b %Y'),'%d-%b-%Y')

The above will need some tweaking because likely if your date is stored as DD-mon-YYYY but you are parsing mon-YYYY unless DD is 00 you will not find a match. So you probably want to take a look at the manual to fix up the formats:

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Thanks. I'm new to this. :) – Colin Spencely Oct 06 '15 at 02:52 — Colin Spencely, Oct 06 '15 at 02:52

score 0 · Answer 2 · answered Oct 06 '15 at 09:02

0

In case of date type datatype in your table, you can use date part function of SQL

datename(year,colName) as [Year]
datename(Month,colName) as [Month]

answered Oct 06 '15 at 09:02

Mike Clark

1,860
14
21

bob dylan · Answer 3 · 2015-10-08T08:05:02.887

create table s (stored_date DATE);
insert into s values ('01-JAN-2015');
insert into s values ('01-DEC-2015');
insert into s values ('02-DEC-2015');
insert into s values ('01-JUN-2015');
insert into s values ('01-MAY-2015');

You can use the between feature for this

select * from s where s.stored_date between to_date('Dec 2015','mon-YYYY') and to_date('Jan 2016','mon-YYYY');

Or

select * from s where to_char(s.stored_date,'Mon-YYYY')='Dec-2015';

You can convert it to a char and extract it that way.

Note with the second method you can replace = with in for multiple months:

select * from s where to_char(s.stored_date,'Mon-YYYY') IN ('Dec-2015', 'Jun-2015');

Which is probably the best method.

sqlfiddle of this: http://sqlfiddle.com/#!4/3e7cf/1

SQL: Trying to extract year and month from a date

3 Answers3