I have an Oracle database where information is logged on a weekly basis.
I want to run a report on a Friday that lists all the information logged
in the previous five days
 
Rather than input the date I would like to code the SQL with 
 
'Where Date is between Today and Today minus five'
 
Dates are stored yyyymmdd
 
I am not sure of the syntax for the SQL
 
I tried 'WHERE Loggeddate BETWEEN Date() And Date()-5'
 
But it does not work
 
Ian Donohue
21 Chaucer Close 
Fareham
Portsmouth
 

Look in your Oracle documentation for date manipulation functions. Date() is
a VBA/VBScript function which would not be available inside your Oracle SQL
statement.

Also, I would consider Date()-5 bad programming practise. Date() returns a
Date, whereas 5 is an integer. You should really use DateAdd(), or subtract
one Date from another.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "donohue ian" <ianmdonohue@c...>
Subject: [asp_databases] Help with Using dates


: I have an Oracle database where information is logged on a weekly basis.
: I want to run a report on a Friday that lists all the information logged
: in the previous five days
:
: Rather than input the date I would like to code the SQL with
:
: 'Where Date is between Today and Today minus five'
:
: Dates are stored yyyymmdd
:
: I am not sure of the syntax for the SQL
:
: I tried 'WHERE Loggeddate BETWEEN Date() And Date()-5'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Wouldn't be
WHERE Loggeddate > SysDate-5

I know the
Select SysDate from DUAL;   gives the current system date.


-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Sunday, November 10, 2002 7:07 PM
To: ASP Databases
Subject: [asp_databases] Re: Help with Using dates


Look in your Oracle documentation for date manipulation functions.
Date() is a VBA/VBScript function which would not be available inside
your Oracle SQL statement.

Also, I would consider Date()-5 bad programming practise. Date() returns
a Date, whereas 5 is an integer. You should really use DateAdd(), or
subtract one Date from another.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "donohue ian" <ianmdonohue@c...>
Subject: [asp_databases] Help with Using dates


: I have an Oracle database where information is logged on a weekly
basis.
: I want to run a report on a Friday that lists all the information
logged
: in the previous five days
:
: Rather than input the date I would like to code the SQL with
:
: 'Where Date is between Today and Today minus five'
:
: Dates are stored yyyymmdd
:
: I am not sure of the syntax for the SQL
:
: I tried 'WHERE Loggeddate BETWEEN Date() And Date()-5'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks for your help.  
Solved problem of transposing dates by using Ken's Date conversion
function off his web site and created one variable then used the
conversion and DateAdd() to create a variable five days earlier.

I will then use the variables in the SQL statement

Ian Donohue
21 Chaucer Close 
Fareham
Portsmouth


-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...] 
Sent: Monday, November 11, 2002 12:41 PM
To: ASP Databases
Subject: [asp_databases] Re: Help with Using dates

Wouldn't be 
WHERE Loggeddate > SysDate-5

I know the 
Select SysDate from DUAL;   gives the current system date.


-----Original Message-----
From: Ken Schaefer [mailto:ken@a...] 
Sent: Sunday, November 10, 2002 7:07 PM
To: ASP Databases
Subject: [asp_databases] Re: Help with Using dates


Look in your Oracle documentation for date manipulation functions.
Date() is a VBA/VBScript function which would not be available inside
your Oracle SQL statement.

Also, I would consider Date()-5 bad programming practise. Date() returns
a Date, whereas 5 is an integer. You should really use DateAdd(), or
subtract one Date from another.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "donohue ian" <ianmdonohue@c...>
Subject: [asp_databases] Help with Using dates


: I have an Oracle database where information is logged on a weekly
basis.
: I want to run a report on a Friday that lists all the information
logged
: in the previous five days
:
: Rather than input the date I would like to code the SQL with
:
: 'Where Date is between Today and Today minus five'
:
: Dates are stored yyyymmdd
:
: I am not sure of the syntax for the SQL
:
: I tried 'WHERE Loggeddate BETWEEN Date() And Date()-5'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~