This content has been marked as final. Show 4 replies
SELECT TO_DATE(DATE1,'YYYY-MM-DD HH24:MI:SS'), DATE2 FROM DATES;This is a huge mistake. If DATE1 is already DATE type then doing a TO_DATE on it is wrong.
Looks like what you want is TO_CHAR, not TO_DATE.
Don't change the NLS settings; they "belong" to the client, not to your code.
TO_CHAR will do an explicit conversion, so the NLS settings will not come into play.
N K wrote:
Problem is, I cannot seem to be able to set NLS settings properly. I need the dates to come out as YYYY-MM-DD HH24:MI:SS but it doesnt seem to work at all. Columns are DATE datatype but no matter what I do (Obviously I am doing something wrong!) I am not able to get HH24:MI:SS output only YYYY-MM-DD.
I've tried using TO_DATE in the query, executing immediate ALTER SESSION SET NLS_DATE.. , using DBMS_SESSION.SET_NLS . Nothing seems to work, any help is appreciated (Maybe I am misplacing the DMBS_SESSION.SET_NLS or something?).
TO_DATE() should do the job, as should ALTER SESSION_SET NLS_DATE_FORMAT if done properly.
CREATE OR REPLACE PROCEDURE TEST (parameters.. ) AS /*variables*/ BEGIN DECLARE CURSOR c_AICTT IS SELECT TO_DATE(DATE1,'YYYY-MM-DD HH24:MI:SS'), DATE2 FROM DATES;
Assuming DATE1 is a DATE datatype you should not be using TO_DATE on it since it is already a date. TO_CHAR() for display purposes might be more appropriate
Check if TO_CHAR helps you.1 person found this helpful
It did help me:
create or replace function write_date_to_file return number is file_name utl_file.file_type; v_date varchar2(30); begin dbms_output.put_line('Into Proc...'); file_name := utl_file.fopen('PURVESH_DIR', 'date.txt','W'); v_date := to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'); utl_file.put_line(file_name, v_date); utl_file.fclose(file_name); dbms_output.put_line('Finshed proc...'); end; File Data:- 21-Jan-2013 20:06:16