Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Datetime Formating - Expand / Collapse
Author
Message
Posted Wednesday, July 27, 2011 9:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 19, 2014 2:20 PM
Points: 140, Visits: 543
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

Post #1149298
Posted Wednesday, July 27, 2011 9:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 12,880, Visits: 31,796
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1149310
Posted Friday, July 29, 2011 3:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1151340
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse