Re: LIKE Clause with dates?

From: Valery Kaluzhny <kvv_at_bipe.dp.ua>
Date: Fri, 25 Jun 1999 09:21:23 +0100
Message-ID: <37733C02.20852BD4@bipe.dp.ua>

> MADEIRA SALLY wrote:
> I have a procedure with paramaters of Procedure GetParticipations(AsOfDate
IN DATE)
> The date is usually the last date of the month.
> I would like to create a LIKe Clause so that my WHERE statement specifies
all records
> WHERE DatePart LIKE '3'..%..'99'
> The AsOfDate being '3/31/1999' How would that be accomplished!
> Thanks in Advance
> Sally
> Actually AsofDate is 3/31/99 so what I need is any date that falls in
between 3/1/99 and 3/31/99
> but I do not want the users to enter to paramenter values just one so it
would be LIKE '3/%/99'

SUGGESTION from : (performance rate)

---

David J. Hazledine: (1)
where datepart between to_date('01'||to_char(asofdate,'MMYYYY'),'DDMMYYYY') and last_day(asofdate)

analog test:
select count(*) from atable where datepart between trunc(AsOfDate,'mm') and last_day(AsOfDate)
result :1729 of 9696 recs / 0.02 sec !!! EXCELLENT

---

"Mark G": (2)
select hiredate from emp where to_char(hiredate,'MM/DD/YYYY') like '02/'||'%'||'/1981'

analog test:
select count(*) from atable where to_char(datepart,'MM/DD/YYYY') like '04/'||'%'||'/1999'
result :1729 of 9696 recs / 0.44 sec

---

James Lorenzen: (3)
  WHERE TRUNC(AsOfDate,'mm') = TRUNC(DatePart,'mm')

analog test:
select count(*) from atable where trunc(AsOfDate ,'MM')=trunc(datepart,'MM') result :1729 of 9696 recs / 0.5 sec

---

Connor McDonald: (4)
where AsOfDate between trunc(datepart,'mm') and add_months(trunc(datepart,'mm'),1)

analog test:
select count(*) from atable where AsOfDate between trunc(datepart,'mm') and add_months(trunc(datepart,'mm'),1)
result :1729 of 9696 recs / 0.7 sec

---

Received on Fri Jun 25 1999 - 03:21:23 CDT