July 27, 2011 at 9:09 am
hi,
- how to get the Last 30 days records from a table in Oracle if the column is in format string -
like
Example - the value in the table is - 1251807219
the value in formatted is "2009-09-01 08:13:39.000"
and after foramting date I want to use this column in where condition
to get the records from last third days
IN SQL SERVER , I can using Date Add - but how cani get the Oracle
Select col1 from tabel
where dateadd(s,convert(int,1251807219)-(4*60*60),'01/01/1970') <getdate()- 30
Please help..,
Thanks
July 27, 2011 at 9:20 am
at our shop, we have to do both SQL and Oracle;
as a result, to make our scripting a little easier, we added this package a long time ago;
this makes the syntax for date functions mostly identical to SQL server, except some definitions have to be in single quotes for SQL server constants.
so for example here's a SQL vs ORACLe example...let me know if this package helps you at all
--SQL
SELECT DATEADD(dd,14,getdate()); --2011-08-10 11:18:44.630
--ORACLE
SELECT DATEADD('dd',14,SYSDATE) FROM DUAL; --10-AUG-11 11.18.03.000000000 AM
The package (i think)
CREATE OR REPLACE PACKAGE "YOURUSER"."SQLSERVER_UTILITIES" AS
FUNCTION DATEADD(DATEINTERVAL VARCHAR2, VALUE NUMBER, DT TIMESTAMP) RETURN TIMESTAMP;
FUNCTION DATEDIFF(DATEINTERVAL VARCHAR2, START_DATETIME DATE, END_DATETIME DATE) RETURN NUMBER;
FUNCTION DATENAME(DATEINTERVAL VARCHAR2, DT TIMESTAMP) RETURN VARCHAR2;
FUNCTION DATEPART(DATEINTERVAL VARCHAR2, DT TIMESTAMP) RETURN NUMBER;
FUNCTION DAY(DT TIMESTAMP) RETURN NUMBER;
FUNCTION MONTH(DT TIMESTAMP) RETURN NUMBER;
FUNCTION YEAR(DT TIMESTAMP) RETURN NUMBER;
FUNCTION SUBSTRING(CHAR_EXPRESION VARCHAR2, START_POSITION NUMBER, SUBSTRING_LENGTH NUMBER) RETURN VARCHAR2;
FUNCTION LEFT(CHAR_EXPRESION VARCHAR2, SUBSTRING_LENGTH NUMBER) RETURN VARCHAR2;
FUNCTION RIGHT(CHAR_EXPRESION VARCHAR2, SUBSTRING_LENGTH NUMBER) RETURN VARCHAR2;
END SQLSERVER_UTILITIES; /
CREATE OR REPLACE PACKAGE BODY "YOURUSERNAME"."SQLSERVER_UTILITIES" AS
FUNCTION DATEADD(DATEINTERVAL VARCHAR2, VALUE NUMBER, DT TIMESTAMP)
RETURN TIMESTAMP
IS
RET_VALUE TIMESTAMP;
PART VARCHAR2(15);
PART_VALUE NUMBER;
BEGIN
PART := UPPER(DATEINTERVAL);
PART_VALUE := TRUNC(VALUE);
IF PART IN ('YEAR', 'YY', 'YYYY') THEN
RET_VALUE := DT + NUMTOYMINTERVAL(PART_VALUE, 'YEAR');
ELSIF PART IN ('QUARTER', 'QQ', 'Q') THEN
RET_VALUE := DT + NUMTOYMINTERVAL(PART_VALUE * 3, 'MONTH');
ELSIF PART IN ('MONTH', 'MM', 'M') THEN
RET_VALUE := DT + NUMTOYMINTERVAL(PART_VALUE, 'MONTH');
ELSIF PART IN ('DAYOFYEAR', 'DY', 'Y', 'DAY', 'DD', 'D', 'WEEKDAY', 'DW', 'W') THEN
RET_VALUE := DT + NUMTODSINTERVAL(PART_VALUE, 'DAY');
ELSIF PART IN ('WEEK', 'WK', 'WW') THEN
RET_VALUE := DT + NUMTODSINTERVAL(PART_VALUE * 7, 'DAY');
ELSIF PART IN ('HOUR', 'HH') THEN
RET_VALUE := DT + NUMTODSINTERVAL(PART_VALUE, 'HOUR');
ELSIF PART IN ('MINUTE', 'MI', 'N') THEN
RET_VALUE := DT + NUMTODSINTERVAL(PART_VALUE, 'MINUTE');
ELSIF PART IN ('SECOND', 'SS', 'S') THEN
RET_VALUE := DT + NUMTODSINTERVAL(PART_VALUE, 'SECOND');
ELSIF PART IN ('MILLISECOND', 'MS') THEN
RET_VALUE := DT + NUMTODSINTERVAL(PART_VALUE / 1000, 'SECOND');
END IF;
RETURN RET_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END DATEADD;
FUNCTION DATEDIFF(DATEINTERVAL VARCHAR2, START_DATETIME DATE, END_DATETIME DATE)
RETURN NUMBER
IS
RET_VALUE NUMBER;
PART VARCHAR2(15);
BEGIN
PART := UPPER(DATEINTERVAL);
IF PART IN ('YEAR', 'YY', 'YYYY','Y') THEN
RET_VALUE := TO_NUMBER(TO_CHAR(END_DATETIME, 'YYYY')) - TO_NUMBER(TO_CHAR(START_DATETIME, 'YYYY'));
ELSIF PART IN ('QUARTER', 'QQ', 'Q') THEN
RET_VALUE := MONTHS_BETWEEN( END_DATETIME, START_DATETIME) /3;
ELSIF PART IN ('MONTH', 'MM', 'M') THEN
RET_VALUE := MONTHS_BETWEEN( END_DATETIME, START_DATETIME);
ELSIF PART IN ('DAY', 'DD', 'D') THEN
RET_VALUE := END_DATETIME - START_DATETIME;
END IF;
RETURN TRUNC(RET_VALUE);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END DATEDIFF;
FUNCTION DATENAME(DATEINTERVAL VARCHAR2, DT TIMESTAMP)
RETURN VARCHAR2
IS
RET_VALUE VARCHAR2(20);
PART VARCHAR2(15);
BEGIN
PART := UPPER(DATEINTERVAL);
IF PART IN ('YEAR', 'YY', 'YYYY') THEN RET_VALUE := TO_CHAR(DT, 'YYYY');
ELSIF PART IN ('QUARTER', 'QQ', 'Q') THEN RET_VALUE := TO_CHAR(DT, 'Q');
ELSIF PART IN ('MONTH', 'MM', 'M' ) THEN RET_VALUE := TO_CHAR(DT, 'MONTH');
ELSIF PART IN ('DAYOFYEAR', 'DY', 'Y') THEN RET_VALUE := TO_CHAR(DT, 'DDD');
ELSIF PART IN ('DAY', 'DD', 'D' ) THEN RET_VALUE := TO_CHAR(DT, 'DD');
ELSIF PART IN ('WEEKDAY', 'DW', 'W') THEN RET_VALUE := TO_CHAR(DT, 'DAY');
ELSIF PART IN ('WEEK', 'WK', 'WW' ) THEN RET_VALUE := TO_CHAR(DT, 'IW');
ELSIF PART IN ('HOUR', 'HH' ) THEN RET_VALUE := TO_CHAR(DT, 'HH24');
ELSIF PART IN ('MINUTE', 'MI', 'N' ) THEN RET_VALUE := TO_CHAR(DT, 'MI');
ELSIF PART IN ('SECOND', 'SS', 'S' ) THEN RET_VALUE := TO_CHAR(DT, 'SS');
ELSIF PART IN ('MILLISECOND', 'MS' ) THEN RET_VALUE := TO_CHAR(DT, 'FF3');
END IF;
RETURN RET_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END DATENAME;
FUNCTION DATEPART(DATEINTERVAL VARCHAR2, DT TIMESTAMP)
RETURN NUMBER
IS
RET_VALUE NUMBER;
PART VARCHAR2(15);
BEGIN
PART := UPPER(DATEINTERVAL);
IF PART IN ('YEAR', 'YY', 'YYYY' ) THEN RET_VALUE := TO_CHAR(DT, 'YYYY');
ELSIF PART IN ('QUARTER', 'QQ', 'Q' ) THEN RET_VALUE := TO_CHAR(DT, 'Q' );
ELSIF PART IN ('MONTH', 'MM', 'M' ) THEN RET_VALUE := TO_CHAR(DT, 'MM' );
ELSIF PART IN ('DAYOFYEAR', 'DY', 'Y') THEN RET_VALUE := TO_CHAR(DT, 'DDD' );
ELSIF PART IN ('DAY', 'DD', 'D' ) THEN RET_VALUE := TO_CHAR(DT, 'DD' );
ELSIF PART IN ('WEEKDAY', 'DW', 'W' ) THEN RET_VALUE := TO_CHAR(DT, 'D' );
ELSIF PART IN ('WEEK', 'WK', 'WW' ) THEN RET_VALUE := TO_CHAR(DT, 'IW' );
ELSIF PART IN ('HOUR', 'HH ' ) THEN RET_VALUE := TO_CHAR(DT, 'HH24');
ELSIF PART IN ('MINUTE', 'MI', 'N' ) THEN RET_VALUE := TO_CHAR(DT, 'MI' );
ELSIF PART IN ('SECOND', 'SS', 'S' ) THEN RET_VALUE := TO_CHAR(DT, 'SS' );
ELSIF PART IN ('MILLISECOND', 'MS' ) THEN RET_VALUE := TO_CHAR(DT, 'FF3' );
END IF;
RETURN RET_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END DATEPART;
FUNCTION DAY(DT TIMESTAMP)
RETURN NUMBER
IS
BEGIN
RETURN DATEPART('DAY', DT);
END DAY;
FUNCTION MONTH(DT TIMESTAMP)
RETURN NUMBER
IS
BEGIN
RETURN DATEPART('MONTH', DT);
END MONTH;
FUNCTION YEAR(DT TIMESTAMP)
RETURN NUMBER
IS
BEGIN
RETURN DATEPART('YEAR', DT);
END YEAR;
FUNCTION SUBSTRING(CHAR_EXPRESION VARCHAR2, START_POSITION NUMBER, SUBSTRING_LENGTH NUMBER)
RETURN VARCHAR2
IS
BEGIN
RETURN SUBSTR(CHAR_EXPRESION, START_POSITION, SUBSTRING_LENGTH);
END SUBSTRING;
FUNCTION LEFT(CHAR_EXPRESION VARCHAR2, SUBSTRING_LENGTH NUMBER)
RETURN VARCHAR2
IS
BEGIN
RETURN SUBSTR(CHAR_EXPRESION, 0, SUBSTRING_LENGTH);
END LEFT;
FUNCTION RIGHT(CHAR_EXPRESION VARCHAR2, SUBSTRING_LENGTH NUMBER)
RETURN VARCHAR2
IS
BEGIN
RETURN SUBSTR(CHAR_EXPRESION, -SUBSTRING_LENGTH, SUBSTRING_LENGTH);
END RIGHT;
END SQLSERVER_UTILITIES; /
Lowell
July 29, 2011 at 3:53 pm
John Paul-702936 (7/27/2011)
- how to get the Last 30 days records from a table in Oracle if the column is in format string -like
Example - the value in the table is - 1251807219
the value in formatted is "2009-09-01 08:13:39.000"
and after foramting date I want to use this column in where condition
to get the records from last third days
IN SQL SERVER , I can using Date Add - but how cani get the Oracle
Select col1 from tabel
where dateadd(s,convert(int,1251807219)-(4*60*60),'01/01/1970') <getdate()- 30
Please help..
Is this part of a Data Warehouse?
If the answer is Yes, check your DIM_DATE dimension table, most probably there is already a 01/01/1970 based number column showing the proper number for every single date. Join against DIM_DATE and filter your 30 days on DIM_DATE.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy