CODEWITHSUNDEEP
sqloracle-database
user393267
user393267

Reputation:

Match partial date in SQL query?

I am looking for a way to match a date, with the one related to records on my db.

I know how to match strings with the LIKE operator, but it doesn't work for dates; unless I search just for a number (say all records at 10, all records from the 21st and so on). As soon as I put minutes for example, the search with LIKE return no records.

How do you actually search for partial date? My objective is to find all records newer than a partial date; in the current day. 

select * from CLIENTS where CLOSING like '%12:30%'

This won't match anything; but if I use just 12 or 30, it works....although it is not doing what I want.

Do I have to convert date in strings? I get a partial date from another process, and I would like to match all the records newer than the partial date.

Upvotes: 2

Views: 6053

Answers (2)

Justin Cave
Justin Cave

Reputation: 231751

If you want loans more recent than today at 12:30pm, you are best served to use date arithmetic rather than relying on string conversion

SELECT *
  FROM clients
 WHERE closing >= trunc(sysdate) + interval '12' hour + interval '30' minute;

or

SELECT *
  FROM clients
 WHERE closing >= trunc(sysdate) + 12.5/24

Here, trunc(sysdate) returns today at midnight. Then you can either add a fractional number of days (1/24 adds one hour so 12.5/24 takes you to 12:30pm) or you can add one or more intervals (you could use to_dsinterval as well to create the interval).

Upvotes: 3

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this query

select * from CLIENTS where 
TO_CHAR(CLOSING , 'dd-mm-yy hh24:mi:ss') like '%12:30%'

or

select * from CLIENTS where 
TO_CHAR(CLOSING , 'hh24:mi') = '12:30'

Upvotes: 6

Related Questions