Re: Getting the last_day in a SQL variable

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Fri, 10 Jul 2009 12:35:47 +0100
Message-ID: <OF52FF2FE1.FCE00BA1-ON802575EF.003F2807-802575EF.003FB36E_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. i0zX+n{+i^ Received on Fri Jul 10 2009 - 06:35:47 CDT

Original text of this message