4
votes
1answer
166 views

date formatting in Oracle

this query: select to_char(sysdate, 'Ddspth MONTH, Yyyysp') from dual will return: Eighteenth MARCH , Two Thousand Fourteen i am wondering if there are any additional formatting keywords that ...
1
vote
2answers
536 views

how to change the default date format in oracle

New to Oracle here, i am working on some assigments but i noticed when i was creating the tables using a script, oracle throws the following error: ORA-01858: a non-numeric character was found where ...
0
votes
1answer
802 views

Different dates Oracle 11g with TOAD

I have the following queries: SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL; /*this returns 2/1/2049. */ SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL; ...
2
votes
2answers
9k views

Comparing date and timestamp variables in a where clause in a procedure

I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing ...
2
votes
2answers
3k views

Setting NLS params in logon trigger don't work in oracle 11g

We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session ...
1
vote
1answer
1k views

Decode query truncates a date

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database SELECT DECODE( TO_CHAR(MIN(DATE), 'YYYYMMDD') , '' , NULL ...
2
votes
2answers
3k views

Oracle Query by Quarter

I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should: select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ... from ... where ...