Date Function in Oracle - URGENT

Riddhi Valecha

Hi.....

I have a table like -

ID	Number	Date1	Date2
1	1234	1/27/2015 4:24:43 PM	1/27/2015 4:43:43 PM
2	1234	1/27/2015 4:24:43 PM	1/27/2015 4:43:43 PM
3	7894	1/27/2015 4:43:43 PM	1/27/2015 4:43:43 PM
4	9080	1/27/2015 4:43:43 PM	1/27/2015 4:43:43 PM

I have to make 2 functions -
1. Function - Return Date
IN_NUMBER varchar2
Query = select Date1 (in dd-mmm-yyyy) format where Number = IN_NUMBER
--------------
My Issue -
How do I get dates such that the time part is eliminated ?? I just want "27-JAN-2015" format for date columns.
-----
If I run the function using dual table, it says - 0 rows returned. But, the table has values.

Please help !

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Riddhi Valecha11y ago
Hi.....
I got an error -

ORA-01821: date format not recognized

Manish Kumar Choudhary11y ago

try this 

select to_char(Date1, 'dd-mmm-yyyy') into p_Group  
from table1

Riddhi Valecha11y ago
I have made it like -
Create or Replace Function My_Date_Function
(
IN_NUMBER VARCHAR2
)
RETURN VARCHAR2
AS
P_GROUP VARCHAR2(5000);

BEGIN
select to_char(Date1, 'dd-MON-yyyy') into p_Group from table1
RETURN P_GROUP;

ENDMy_Date_Function;
/
------------------
But, I am getting - 0 rows fetched when I run the query.

Manish Kumar Choudhary11y ago

select to_char(col_name, 'yyyy-mm-dd hh24:mi:ss') from tab_name
just change the DateTime Format

Riddhi Valecha11y ago
Hi..

I have to make this in TOAD (Oralce).

The Columns have datatype - DATE.

I have to keep VARCHAR2 as datatype of the function.
Manish Kumar Choudhary11y ago
Go through following link it will help you

http://www.sqlusa.com/bestpractices/datetimeconversion/