Re: dynamic date with sysdate

jpollone_at_primeco.com schrieb:
>
> Hello,
>
> I am using a third-party product to select records between 2 dynamic dates.
> In other words I would like one week's worth of records, three weeks ago.
> Since this is my first attempt with SQL, I am encountering the following
> error:
>
> ORA-01843: not a valid month
>
> I have also attempted using to_char with sysdate only and still receive the
> same error. Am I (hopefully) doing something wrong, or could this be a short
> coming of the third-party query tool?
>
> Thanks,
> Joseph Pollone
>
> SQL:
>
> select Liable_Accounts."LIABLE_ACCT_ID",
> Liable_Accounts."LIABLE_ACCT_OPEN_DT",
> Liable_Accounts."LIABLE_ACCT_CLOSE_DT"
>
> from "PWRMRT1"."LIABLE_ACCOUNTS" Liable_Accounts
>
> where
> (
> Liable_Accounts."LIABLE_ACCT_OPEN_DT" between to_char(sysdate - 21,
> 'YYYY-MM-DD') and to_char(sysdate - 14, 'YYYY-MM-DD')
> )
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Hi,

with to_char you have to specify the date format you use. This is either the date format specified by the NLS_DATE parameter or by the NLS_LANG parameter. If you specify another format in the call to to_char incorrect transforming will be the result. If the field containing the date is a real date field then you get the records of the last seven days till yesterday by issuing something like:

Select

	myfield1,
	myfield2
from
	mytable
where
	mydatefield 	between 
				trunc(sysdate-7)
			and	
				trunc(sysdate)

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Wed Jan 13 1999 - 00:59:40 CST