Dates issue

Hi ForumI was wondering if someone an answer a query for me, i am trying to do a query from the database based on dates for the amount of user activity on our system based on login, i have the Oracle Sql for this and can get results back when i do the search between dates using the date chooser boxes which i get the user to fill in.My problem though is i want to do an automated one too which does not require dates to be selected but are populated automatically, i have been using the AuditDate between current_date -5 And current_date, however the problem here is it does the search based on the actual current date and time i.e if i run at 3:00 pm it brings me back results from today at 3pm to 5 days previous at 3pm what i want to do is just return exactly 5 days, how do i manulipate the date to give me full days and not from the time i run the search, i know it is a format issue but i am not sure how to perform the format to do this.Any help would be greatly appreciated.RegardsMike Fennell.

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Mike,

The AuditDate column has a datatype of timestamp.

So you will need to do something like the following (in Oracle)...

where to_char(AuditDate,'YY-MM-DD') >= to_char(sysdate-5,'YY-MM-DD')

Regards

Lindsay

European Escalation Team

OpenText

From: eLink Discussion: Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 April 20, Wed 10:58
To: eLink Recipient
Subject: Dates issue

Dates issue

Posted by michael.fennell@fsa.gov.uk (Fennell, Michael) on 2011/04/20 05:57

Hi Forum

I was wondering if someone an answer a query for me, i am trying to do a query from the database based on dates for the amount of user activity on our system based on login, i have the Oracle Sql for this and can get results back when i do the search between dates using the date chooser boxes which i get the user to fill in.
My problem though is i want to do an automated one too which does not require dates to be selected but are populated automatically, i have been using the AuditDate between current_date -5 And current_date, however the problem here is it does the search based on the actual current date and time i.e if i run at 3:00 pm it brings me back results from today at 3pm to 5 days previous at 3pm what i want to do is just return exactly 5 days, how do i manulipate the date to give me full days and not from the time i run the search, i know it is a format issue but i am not sure how to perform the format to do this.

Any help would be greatly appreciated.

Regards
Mike Fennell.

Michael_Fennell

Hi LindsayThanks for your reply that worked perfect i knew it was a format issue but was not sure how to exactly approach it.RegardsMike.