Re: Getting the last_day in a SQL variable

2009/7/10 Ian Cary <ian.cary_at_ons.gsi.gov.uk>

> Well last_day works on a date data type so what you can do is
>
> select count(distinct(ppsn))
> from award_result
> where to_date(date_awarded, 'DD-MON-YY') >=
> to_date('01-&month-&year','dd-mon-yy')
> and to_date(date_awarded, 'DD-MON-YY') <=
> last_day(to_date('01-&month-&year','dd-mon-yy'))
>
> alternatvely as you are already applying a function to date_awarded you
> could use
>
> select count(distinct(ppsn))
> from award_result
> where trunc(to_date(date_awarded, 'DD-MON-YY'),'MM') =
> to_date('01-&month-&year','dd-mon-yy')
>
> Cheers,
>
> Ian
>
>
>
>
> |---------+----------------------------->
> | | painterman_at_gmail.c|
> | | om |
> | | Sent by: |
> | | oracle-l-bounce_at_fr|
> | | eelists.org |
> | | |
> | | |
> | | 10/07/2009 12:20 |
> | | Please respond to |
> | | painterman |
> | | |
> |---------+----------------------------->
>
> >--------------------------------------------------------------------------------------------------------------|
> |
> |
> | To: oracle-l_at_freelists.org
> |
> | cc:
> |
> | Subject: Getting the last_day in a SQL variable
> |
>
> >--------------------------------------------------------------------------------------------------------------|
>
>
>
>
> Hi guys,
>
> I'm trying to do a sql script (please see below) but i cant figure out how
> to get in a variable the value of the LAST_DAY in a month... any tips?
>
> accept month prompt 'Enter Month [MON]:';
> accept year prompt 'Enter Year [YY]:';
>
> def lastday=LAST_DAY(&month);
>
> select count(distinct(ppsn))
> from award_result
> where to_date(date_awarded, 'DD-MON-YY') >= '01-&month-&year'
> and to_date(date_awarded, 'DD-MON-YY') <= '&lastday-&month-&year';
>
>
>
> Thanks!!
>
> David
>
>
> This email was received from the INTERNET and scanned by the Government
> Secure Intranet anti-virus service supplied by Cable&Wireless in
> partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
> case of problems, please call your organisation’s IT Helpdesk.
> Communications via the GSi may be automatically logged, monitored and/or
> recorded for legal purposes.
>
> For the latest data on the economy and society consult National Statistics
> at http://www.statistics.gov.uk
>
>
> *********************************************************************************
>
>
> Please Note: Incoming and outgoing email messages are routinely monitored
> for compliance with our policy on the use of electronic communications
>
> *********************************************************************************
>
>
> Legal Disclaimer : Any views expressed by the sender of this message are
> not necessarily those of the Office for National Statistics
>
> *********************************************************************************
>
>
> The original of this email was scanned for viruses by the Government Secure
> Intranet virus scanning service supplied by Cable&Wireless in partnership
> with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi
> this email was certified virus free.
> Communications via the GSi may be automatically logged, monitored and/or
> recorded for legal purposes.
>

--
http://www.freelists.org/webpage/oracle-l