CODEWITHSUNDEEP
sqloracle-database
user8659343
user8659343

Reputation:

SQL Querying dates using only month and year

I'm trying to query my table to return the 'sessions' that are booked for june 2017. (06-17) this is my query so far:

SELECT SESSIONID,SESSIONDATE,SESSIONPRICE
FROM CONFERENCESESSION
WHERE SESSIONDATE = TO_DATE('06-17', 'MM-YY')
ORDER BY SESSIONDATE DESC;

however this returns nothing. it does work if i query a specific date, for example 

WHERE SESSIONDATE = TO_DATE('6-06-17', 'DD-MM-YY')

my table data looks like this:

INSERT INTO CONFERENCESESSION VALUES ('SS01','B1','R1','S1',TO_DATE('3-05-17','DD-MM-YY'),12.50);
INSERT INTO CONFERENCESESSION VALUES ('SS02','B2','R1','S2',TO_DATE('4-06-17','DD-MM-YY'),19.45);
INSERT INTO CONFERENCESESSION VALUES ('SS03','B1','R2','S2',TO_DATE('13-05-17','DD-MM-YY'),12.69);
INSERT INTO CONFERENCESESSION VALUES ('SS04','B4','R2','S4',TO_DATE('13-06-17','DD-MM-YY'),14.56);
INSERT INTO CONFERENCESESSION VALUES ('SS05','B3','R2','S5',TO_DATE('23-05-17','DD-MM-YY'),14.56);
INSERT INTO CONFERENCESESSION VALUES ('SS06','B3','R1','S5',TO_DATE('3-06-17','DD-MM-YY'),16.32);
INSERT INTO CONFERENCESESSION VALUES ('SS07','B4','R2','S3',TO_DATE('13-05-17','DD-MM-YY'),21.78);
INSERT INTO CONFERENCESESSION VALUES ('SS08','B1','R2','S2',TO_DATE('6-06-17','DD-MM-YY'),16.82);
INSERT INTO CONFERENCESESSION VALUES ('SS09','B2','R3','S4',TO_DATE('13-05-17','DD-MM-YY'),17.90);
INSERT INTO CONFERENCESESSION VALUES ('SS10','B4','R1','S3',TO_DATE('6-06-17','DD-MM-YY'),16.37);

any help is greatly appreciated. thank you so much !

Upvotes: 0

Views: 90

Answers (4)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Truncate the date to the "month" level and compare that way:

SELECT SESSIONID,SESSIONDATE,SESSIONPRICE
  FROM CONFERENCESESSION
  WHERE TRUNC(SESSIONDATE, 'MONTH') = TO_DATE('06-2017', 'MM-YYYY')
  ORDER BY SESSIONDATE DESC;

SQLFiddle here to see what the effects are.

By the way, this is a VERY expensive query in that you need to execute a function against every row in the table. If you don't have a function-based index specifically in place to service this query it may be very slow. A better method is to use a ranged query, as shown below:

SELECT SESSIONID,SESSIONDATE,SESSIONPRICE
  FROM CONFERENCESESSION
  WHERE SESSIONDATE BETWEEN TO_DATE('01-06-2017', 'DD-MM-YYYY')
                        AND TO_DATE('01-07-2017', 'DD-MM-YYYY') - INTERVAL '1' SECOND
  ORDER BY SESSIONDATE DESC;

Here the end-points of the query ('01-06-2017' and '01-07-2017') only need to be computed once - after that Oracle uses simple DATE comparisons which perform well.

Keep in mind that when using BETWEEN the endpoints are both considered to be "in" the range, so we need to subtract one second from (in this case) July 1st to ensure that the query doesn't pick up any unwanted rows with a SESSIONDATE in the first second of July.

Best of luck.

Upvotes: 1

Ravi
Ravi

Reputation: 31397

If you are using Oracle, then use TO_CHAR instead.

Example:

WHERE TO_CHAR(SESSIONDATE,'MM-YY')='06-17'

>>>Demo<<<

If you are using SQL Server, you can use CONVERT

CONVERT(VARCHAR(4), SESSIONDATE, 12) ='1706'

Above conversion will return as 'YYMM'.

Upvotes: 1

stefan
stefan

Reputation: 2252

Suppose your table contains the following data (using MS SQL server 2016):

SS01    B1  R1  S1  03/05/2017 00:00:00 12.50
SS02    B2  R1  S2  04/06/2017 00:00:00 19.45
SS03    B1  R2  S2  13/05/2017 00:00:00 12.69
SS04    B4  R2  S4  13/06/2017 00:00:00 14.56
SS05    B3  R2  S5  23/05/2017 00:00:00 14.56
SS06    B3  R1  S5  03/06/2017 00:00:00 16.32
SS07    B4  R2  S3  13/05/2017 00:00:00 21.78
SS08    B1  R2  S2  06/06/2017 00:00:00 16.82
SS09    B2  R3  S4  13/05/2017 00:00:00 17.90
SS10    B4  R1  S3  06/06/2017 00:00:00 16.37

You could use a query that looks a bit like ...

select sessionid sid_, sessiondate date_, sessionprice price_
from cs
where month(sessiondate) = 6
and year(sessiondate) = 2017
order by sessiondate desc;

Output (all "June" sessions)

sid_    date_               price_
SS04    13/06/2017 00:00:00 14.56
SS08    06/06/2017 00:00:00 16.82
SS10    06/06/2017 00:00:00 16.37
SS02    04/06/2017 00:00:00 19.45
SS06    03/06/2017 00:00:00 16.32

See dbfiddle here.

Upvotes: -1

Jinesh Shah
Jinesh Shah

Reputation: 952

You can extract month and year and pass it in where clause. You write query as follow,

 SELECT SESSIONID,SESSIONDATE,SESSIONPRICE, EXTRACT (Month from SESSIONDATE) as SESSIONDATE_Month,EXTRACT (Year from SESSIONDATE) as SESSIONDATE_Year from CONFERENCESESSION
WHERE  EXTRACT (Month from SESSIONDATE) = 06 and EXTRACT (Year from SESSIONDATE) = 2017 
ORDER BY SESSIONDATE DESC;

Upvotes: 0

Related Questions