Re: Selecting results for month to date

VB wrote:
> I have been asked to modify an existing report for deliveries in the
> last 24 hours to be deliveries for the month to date.

If you have a report to modify, it might be beneficial to post the SQL statement(s) that needs to be modified, along with the modifications you desire to see.

> At the moment the where statement includes sysdate -1

(SYSDATE - 1) is a way of defining "yesterday". SYSDATE is the current date and time. Subtracting 1 subtracts one complete day. So more precisely, (SYSDATE - 1) is 24 hours prior to the current date and time.

> Is there a more elegant way to select all diliveries after midnight on
> the first day of the month?

The current month? First, get the current month from SYSDATE:

TO_CHAR(sysdate,'MM')

Next, get the current year:

TO_CHAR(sysdate,'YYYY')

Now that you know the current month and year, you can formulate the first day of the current month:

TO_DATE(TO_CHAR(sysdate,'MM')||'/01/'||TO_CHAR(sysdate,'YYYY'),'MM/DD/YYYY')

You can use the above for comparisons in your query. The above will return the first day of the current month. Now, just find dates since then with the >= operator.

> Sorry to be an ignorant questioner, but our SQL person has left the
> company so all help is greatly appreciated.

Familiarizing yourself with the docs can be very beneficial. The following shows how to do date arithmetic:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3826

The SQL Reference Guide contains more information, along with the valuable functions:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown