SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Datetime Formating -


Datetime Formating -

Author
Message
John Paul-702936
John Paul-702936
SSC-Addicted
SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)

Group: General Forum Members
Points: 406 Visits: 633
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
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74585 Visits: 40983
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14627 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search