Remember Remedy stores Create_Date and Date_Modified as the number of seconds
since Jan 1, 1970 so you have to convert the Oracle SYSDATE data.
Here are the Oracle functions I use to convert to and from "Epoch" (number of
seconds since Jan 1, 1970). Just change the 'America/Chicago' to the time zone
you are using.
CREATE OR REPLACE FUNCTION "FROM_EPOCH" (secs IN NUMBER)
RETURN DATE
IS
lDate DATE;
BEGIN
lDate := (From_TZ((TO_DATE('01/01/1970', 'dd/mm/yyyy')+(secs/86400)),'GMT')
AT TIME ZONE 'America/Chicago');
RETURN lDate;
END From_Epoch;
CREATE OR REPLACE FUNCTION "TO_EPOCH" (lDate IN DATE)
RETURN NUMBER
IS
Seconds NUMBER;
dte1 TIMESTAMP;
dte2 TIMESTAMP;
BEGIN
dte1 := new_time(lDate, to_char( from_tz(cast (lDate as timestamp),
'America/Chicago'),'TZD'),'GMT');
dte2 := TO_TIMESTAMP_TZ('01/01/1970 '|| TZ_Offset('Greenwich'), 'MM/DD/YYYY
TZH:TZM');
Seconds := (((dte1-0) - (dte2-0)) * 86400);
RETURN Seconds;
END To_Epoch;
For querying thru SQL you can do something like
Select * from TABLE_dbVIEW_NAME
Where Create_Date between ( TO_EPOCH(TRUNC(SYSDATE) -
(TO_CHAR(SYSDATE,'DD')-1)) AND TO_EPOCH(TRUNC(SYSDATE)) );
Fred
________________________________
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED]
On Behalf Of Tadeu Augusto Dutra Pinto
Sent: Tuesday, April 22, 2008 8:46 AM
To: [email protected]
Subject: Using sysdate on Oracle 10g
**
Hi people...
I'm not an expert in SQL statements, and I'd like to get some suggestions about
a search that I wanted to do on a table...
I would like to get all records that are less than current date (ex:
22/04/2008) and more than the first day of the current month (ex: 01/04/2008)...
How can I do this with SQL Statements ??
I'm using Oracle 10g with functions like 'sysdate' to get the correct data
since 01/01/1970...
thanks for any reply...
Att,
Tadeu Augusto Dutra Pinto
-----------------------------------------------------------------
IT Web Services ATM
Cinq Technologies
http://www.cinq.com.br
<https://webmail.cinq.com.br/exchweb/bin/redir.asp?URL=http://www.cinq.com.br/>
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Fone: 41 3018-2833 - Cinq
-----------------------------------------------------------------
Confiabilidade, Inovação e Qualidade em T.I.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
