Datetime Formating -

  • John Paul-702936

    SSCrazy

    Points: 2182

    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

    SSC Guru

    Points: 323352

    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

    SSC Guru

    Points: 66773

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply