select only year and month in where clause

Question

I have this value in my database 101101-2017-06-000 It is value for column lddap_no I want to select value with year equal to current year and current month my query is

SELECT lddap_no FROM lddap WHERE YEAR(lddap_no) = '2017' AND MONTH(lddap_no) = '06' ORDER BY sys_id DESC LIMIT 1

I used YEAR() and MONTH() respectively but it didnt work I get no result even if I have one entry.

How to correctly compare year and month from a column value that is not formatted date.

UPDATE

Used WHERE YEAR(substring(lddap_no,7,10)) = '2017' AND MONTH(substring(lddap_no,12,13)) = '06' Still same no result found

tried using this

WHERE YEAR(SUBSTRING(CAST(lddap_no AS VARCHAR(100)), 7, 10)) = '2017'  AND MONTH(SUBSTRING(CAST(lddap_no AS VARCHAR(100)), 12, 13)) = '06'

mysql is what i am using but i also want to know in sql i think they are the same anyway — Giant, Jun 08 '17 at 08:18
Your value is not a date or datetime value so the functions MONTH and YEAR will not work. — Pieter B, Jun 08 '17 at 08:20
Possible duplicate of [Get records of current month](https://stackoverflow.com/questions/21276975/get-records-of-current-month) — Chonchol Mahmud, Jun 08 '17 at 08:20
i tried to use **WHERE YEAR(substring(lddap_no,7,10)) = '2017' AND MONTH(substring(lddap_no,12,13)) = '06'** stil same result what is wrong in my where clause — Giant, Jun 08 '17 at 08:24
Do you mean *ldap*? Or is it a custom format? I suppose it's better to store a real date in a real date field. So it's easy to select date based data. — schellingerht, Jun 08 '17 at 08:24
no it is a column in database . its value is `101101-2017-06-000` which is not a date format that is why it didnt work. I believe this is not a duplicate of the one mentioned above @schellingerht — Giant, Jun 08 '17 at 08:25

score 1 · Accepted Answer · answered Jun 08 '17 at 08:34

This is an XY problem. The real problem here is that you are storing your data in the wrong format. Store dates as dates or datetimes, then you can start querying using the MySQL date functions.

In the meantime since it's a varchar, for a cheap hack, you could try a LIKE clause:

......WHERE ldap_no LIKE "%-2017-06-%"

Might be worth a shot.

score 0 · Answer 2 · answered Jun 08 '17 at 08:34

0

Maybe you can try :

SELECT * FROM lddap WHERE lddap_no LIKE '%2017-06%';

answered Jun 08 '17 at 08:34

Thomas Lefetz

129
5

select only year and month in where clause

2 Answers2