[프로그래머스 sql] where ~ like는 문자열 타입 비교, date는 다르게

- 왜 틀린 건지 모르겠는데..?

 

수정 전 

SELECT b.TITLE, b.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS, 
		date_format(r.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
from USED_GOODS_BOARD b
join USED_GOODS_REPLY r using(BOARD_ID)
where r.CREATED_DATE like '2022-10-%'
order by r.CREATED_DATE asc, b.TITLE asc;

 

 

수정 후1
- created_ date 칼럼은 date 타입인데,

where ~ like는 문자열 타입을 비교할 때 쓰는 것이라서 돌아가는 것처럼 보여도 문제가 있음. 

- date_format을 그대로 사용해서 해당하는 값만 출력 

SELECT b.TITLE, b.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS, 
        date_format(r.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
from USED_GOODS_BOARD b
join USED_GOODS_REPLY r using(BOARD_ID)
where date_format(r.CREATED_DATE, '%Y-%m') = '2022-10' -- 변경
order by r.CREATED_DATE asc, b.TITLE asc;

 

수정 후2

SELECT b.TITLE, b.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS, 
        date_format(r.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
from USED_GOODS_BOARD b
join USED_GOODS_REPLY r on b.BOARD_ID = r.BOARD_ID -- 타입이 같지만, 혹시나 수정해봄
where r.CREATED_DATE between '2022-10-01' and '2022-10-31' -- 얘도 범위로 수정함
order by r.CREATED_DATE asc, b.TITLE asc;

 

..?

- 왜 여전히 안될까?

 

 

수정 후3

SELECT b.TITLE, b.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS, 
        date_format(r.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
from USED_GOODS_BOARD b
join USED_GOODS_REPLY r on b.BOARD_ID = r.BOARD_ID
WHERE r.CREATED_DATE BETWEEN '2022-10-01 00:00:00' AND '2022-10-31 23:59:59' -- 시간도 들어간 것으로 변경
order by r.CREATED_DATE asc, b.TITLE asc;

 

- 그래도 틀렸다는데..?

 

.......아오..

게시글 기준.. 날짜 잖아... 댓글 날짜 아니고.

SELECT b.TITLE, b.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS, 
        date_format(r.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
from USED_GOODS_BOARD b
join USED_GOODS_REPLY r on b.BOARD_ID = r.BOARD_ID
WHERE b.CREATED_DATE LIKE '2022-10%' -- 게시글 기준!. 결국 처음이 맞았네
order by r.CREATED_DATE asc, b.TITLE asc;