Archive

Posts Tagged ‘day’

TO_CHAR(…, ‘D’)

Tuesday, 23 August, 2011 4 comments
to_char(nchar-clob-or-nclob)

to_char(datetime-or-interval)
to_char(datetime-or-interval, 'format-string')
to_char(datetime-or-interval, 'format-string', 'nlsparam')

to_char(number)
to_char(number,'format-string')
to_char(number,'format-string', 'nlsparam')

How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ?
solution1
With to_char()

alter session set nls_territory=germany;
select to_char(sysdate,’DAY D’) from dual;
TUESDAY 2

solution2
With decode()

The abbrivation of a day’s name (such as SUN, MON … can be retrieved with the format specifier DY. As to_char is sensitive to the actual nls setting, I use nls_date_language=english to make sure that the english abbreviation is returned, regardless of the nls setting.

select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ’1′, ‘TUESDAY’, ’2′, ‘…’)) from dual;

solution3
With mod()
As a reference, I take monday Jan 1st, 1000.

select mod(trunc(sysdate)-date ’1000-01-01′,7)+1 from dual;
  • bonus )))
  • How do I trunc date to current monday?

    trunc(date, ‘D’) or here is with my solution with 1000-01-01:

    select trunc((sysdate-date ’1000-01-01′)/7)*7+date ’1000-01-01′ from dual;
    

    thanks to jan-marcel idea, I found that one
    trunc(date,’IW’) for current monday and date-trunc(date,’IW’)+1 for day number

    Spelling a number

    select to_char(to_date('429','J'),'Jsp') from dual;
    TO_CHAR(TO_DATE('429','J
    ------------------------
    Four Hundred Twenty-Nine
    

    from1
    from2

    Advertisements
    Categories: PL/SQL Tags: , ,

    How does one get the time difference between two date columns?

    Friday, 12 August, 2011 Leave a comment

    Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.

    Let’s investigate some solutions. Test data:

    CREATE TABLE dates (date1 DATE, date2 DATE);
    INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);
    INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);
    INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);
    SELECT (date1 - date2) FROM dates;
    
    DATE1-DATE2
    -----------
    1
    .041666667
    .000694444
    

    Solution 1

    SELECT floor(((date1-date2)*24*60*60)/3600)
    || ' HOURS ' ||
    floor((((date1-date2)*24*60*60) -
    floor(((date1-date2)*24*60*60)/3600)*3600)/60)
    || ' MINUTES ' ||
    round((((date1-date2)*24*60*60) -
    floor(((date1-date2)*24*60*60)/3600)*3600 -
    (floor((((date1-date2)*24*60*60) -
    floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
    || ' SECS ' time_difference
    FROM dates;
    
    TIME_DIFFERENCE
    --------------------------------------------------------------------------------
    24 HOURS 0 MINUTES 0 SECS
    1 HOURS 0 MINUTES 0 SECS
    0 HOURS 1 MINUTES 0 SECS
    

    An enhancement to solution 1

    SELECT floor((date1-date2)*24)
    || ' HOURS ' ||
    mod(floor((date1-date2)*24*60),60)
    || ' MINUTES ' ||
    mod(floor((date1-date2)*24*60*60),60)
    || ' SECS ' time_difference
    FROM dates;</code>
    
    TIME_DIFFERENCE
    --------------------------------------------------------------------------------
    24 HOURS 0 MINUTES 0 SECS
    1 HOURS 0 MINUTES 0 SECS
    0 HOURS 1 MINUTES 0 SECS
    

    Solution 2

    If you don’t want to go through the floor and ceiling math, try this method (contributed by Erik Wile):

    SELECT to_number( to_char(to_date('1','J') +
    (date1 - date2), 'J') - 1) days,
    to_char(to_date('00:00:00','HH24:MI:SS') +
    (date1 - date2), 'HH24:MI:SS') time
    FROM dates;
    
    DAYS TIME
    ---------- --------
    1 00:00:00
    0 01:00:00
    0 00:01:00
    

    Solution 3: If u want an easier method, use numtodsinterval()

  • Submitted by Shilpa Petrim
  • NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are ‘DAY’, ‘HOUR’, ‘MINUTE’ and ‘SECOND’.

    This function converts the number x into an INTERVAL DAY TO SECOND datatype.

    select numtodsinterval(date1-date2,'day') time_difference from dates;
    TIME_DIFFERENCE
    ----------------------------------------------------------------
    +000000001 00:00:00.000000000
    +000000000 01:00:00.000000000
    +000000000 00:01:00.000000000
    

    from

    Categories: PL/SQL Tags: , , , ,