finding it difficult to format Date and Timestamp in my script.
v_date1 Date;
v_date2 timestamp;
Assume I have a created_date field and value is '31-03-2017 18:02:05'
select c1 into v_date1 from table_a;
Gives me - '31-03-2017'
select c1 into v_date2 from table_a;
Gives me - '31-03-2017 06:02:05 PM'
I am trying to fetch data through a cursor and thereby iterate it for few validation.
How to make sure v_date2 will only consider Date and Time?
Please help.
To understand this differenence you need to first understand how session session nls settings work.
SQL> @C:\Users\44011713\Desktop\so_test.sql
SQL> SET SQLBL ON;
SQL> SET ECHO ON;
SQL> SET FEEDBACK ON;
SQL>
SQL>
SQL> --To understand this differenence you need to first understand how session nls settings work.
SQL>
SQL> SELECT * FROM nls_session_parameters
2 where parameter = 'NLS_DATE_FORMAT';
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
MM/DD/YYYY
1 row selected.
SQL>
SQL>
SQL> SELECT CURRENT_DATE FROM DUAL;
CURRENT_DA
----------
03/22/2017
1 row selected.
SQL> -- Since we are fetcing date value and the session format mask is MM/DD/YYYY so the output --will be aligning to this format only
SQL>
SQL> SELECT * FROM nls_session_parameters
2 where parameter = 'NLS_TIMESTAMP_FORMAT';
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT
MM/DD/YYYY HH24:MI:SS.FF6
1 row selected.
SQL>
SQL>
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'MM/DD/YYYY HH24:MI:SS.FF6';
Session altered.
SQL>
SQL> SELECT LOCALTIMESTAMP FROM DUAL;
LOCALTIMESTAMP
---------------------------------------------------------------------------
03/22/2017 09:53:54.133795
1 row selected.
SQL> -- Since we are fetcing date value and the session format mask is MM/DD/YYYY --HH24:MI:SS.FF6 so the output will be aligning to this format only
SQL>
SQL> --Now lets tweak some settings :P
SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
Session altered.
SQL>
SQL> SELECT CURRENT_DATE FROM DUAL;
CURRENT_DATE
-------------------
03/22/2017 09:53:54
1 row selected.
SQL> -- And now the format aligns to the forced the setting done by ALTER SESSION.
SQL>
SQL> -- So above example illustrates how the the format ca be changed for session
SQL>
SQL> --Hope this helps.
SQL> spool off;
DATE datatype will store date along with timestamp. The timestamp datatype will however store fraction of seconds as well.
The reason you are not seeing time for DATE datatype is because of your NLS settings. Change them to below
ALTER SESSION SET nls_date_format='DD-MM-YYYY HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MM-RR HH24.MI.SSXFF';
Use timestamp only if fraction of seconds matter in your code. DATE will work for any other use.
I think you have misunderstood date and timestamp
select c1 into v_date1 from table_a;
Gives you a date object
v_date2 timestamp;
Gives you a timestamp object
Both these objects contain date and time components. Timestamp contains more detailed (fraction of second information) as well.
If you want date only you can use a number of built in functions...
For the full information...
select systimestamp from dual;
select sysdate from dual;
but be aware that your query environment might not display the full date information depending on its settings.
To remove the time component...
select trunc(systimestamp) from dual;
select trunc(sysdate) from dual;
or for more control
select trunc(systimestamp, 'dd') from dual;
select trunc(sysdate, 'dd') from dual;
where the 'dd' can be replaced with other values such as 'yyyy' if you want to get the first day of the year or 'mm' for the first day of the month.
or rounding rather than removing (again with the optional values and day being the default)
select round(systimestamp, 'dd') from dual;
select round(sysdate, 'dd') from dual;
However all these functions still return data/timestamp values so they still contain the time information - it is just manipulated.
If all you want is a string with the day you need to use to_char
select to_char(systimestamp, 'dd-mm-yyyy') from dual;
select to_char(sysdate, 'dd-mm-yyyy') from dual;
using what ever date format you prefer.
