Re: Problem with a date in the query !!

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 27 Dec 2005 15:43:55 -0800
Message-ID: <1135727035.868576.148900@f14g2000cwb.googlegroups.com>

gab wrote:
> Hello !!
>
> I've got a problem with a query,I would like to select some data from "
> TbOrder"
> table where the period date is from the 01/04/(current year) to 31/March
> /( year +1 )
>
> This is my query but it doesn't work :
>
> Select *
> from TbOrder a
> WHERE TO_CHAR(a.dat_cde,'DDMMYYYY')
> between TO_CHAR('0104'||TO_CHAR(SYSDATE,'YYYY'),'DDMMYYYY')
> to TO_CHAR('3103'||TO_CHAR(SYSDATE,'YYYY')+1,'DDMMYYYY')

Perhaps something like

WHERE a.dat_cde BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'YY'),3)

                  AND     ADD_MONTHS(TRUNC(SYSDATE,'YY'),16) -1

although some comments in the code would probably be a good idea.

If you compare character strings like '01042005' and '31031876', then you will find that any string beginning with '3' is greater than any string beginning with '0', regardless of what date you think the string refers to.

btw a useful convention is to put all SQL keywords in uppercase and everything else in lowercase. An alternative approach is to put all SQL keywords in lowercase and everything else in uppercase. Some argue that mixedCase is meaningful for object types. "Select", "TbOrder", "WHERE" and "between" in the same query however is just confusing. My 2c... Received on Tue Dec 27 2005 - 17:43:55 CST