Re: Sql Querry related to date datatype

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Fri, 03 Dec 1999 15:18:08 GMT
Message-ID: <3847de53.10726884@newshost.us.oracle.com>

On Fri, 03 Dec 1999 10:43:33 GMT, Parvinder Singh <parora_at_questone.com> wrote:

>
>Hi all
>
> How can i get all the months from jan1997 to mar1999 ...
>or for that matter any range of months...within a single SQL querry
>or in other words i have the date 01-Jan-1997 and 01-Mar-1999 and
>i want all the intermediate dates of every month ...like 01-Feb1997
>01-Mar-19997 , 01-Apr-1997 ..
>.and so on ..(just the starting dates of the intermediate months)
>

SELECT *
  FROM foo
 WHERE date_col BETWEEN TO_DATE('01011997','MMDDYYYY')    AND TO_DATE('03011999')
   AND TO_CHAR(date_col,'DD') = '01';

Keep in mind, the TO_CHAR will potentially force a full-table scan unless you supply additional criteria in your query or use function-based indexes (available in Oracle8i).

>Thanks in advance
>Regards
>
>Parvinder
>
>(also reply to parora_at_questone.com)
>

Thanks!

Joel

Joel R. Kallman Oracle Service Industries

Columbus, OH                             http://govt.us.oracle.com

jkallman@us.oracle.com                   http://www.oracle.com

---

The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Fri Dec 03 1999 - 09:18:08 CST