Re: Last Full Three Months?

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Thu, 02 Jun 2005 05:26:27 GMT
Message-ID: <7Mwne.4139$s64.2865_at_newsread1.news.pas.earthlink.net>


Dan wrote:
> I have sales line item detail like
>
> invoicedate, custid, ordernum
>
> Is there a way using SQL to only return records for the last full three
> months? For example, if today is 2/4/05, I would want records with
> invoicedate between 11/1/04 and 1/31/05.

Well, you can probably evaluate 'first day of current month', then subtract 1 day, and this gives you the 'last day of previous month'. You and also subtract 3 months from the 'first day of current month', and this give you the first day of the range. So, the question is how to evaluate 'first day of current month'.

There are probably about as many ways of doing that (evaluate first day of given month) as there are DBMS - actually, more; each DBMS probably has several ways to do it. I can't immediately locate the SQL standard functions for doing so.

In IBM Informix Dynamic Server:

MDY(MONTH(TODAY),1,YEAR(TODAY)) - 1 -- UNITS DAY -- is optional MDY(MONTH(TODAY),1,YEAR(TODAY)) - 3 UNITS MONTH There are some internal gymnastics in progress on the second line, but it will work. MDY is a 'constructor' or 'selector' - it takes month number, day number and year number and returns a DATE. MONTH and YEAR return the corresponding components of the date argument. TODAY returns today's date.

As Paul said, a more focussed question in an appropriate DBMS-specific news group might yield better results.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/
Received on Thu Jun 02 2005 - 07:26:27 CEST

Original text of this message