Proper usage of Date and Timestamp Oracle datatypes

Question

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.

Answer 1

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;

Answer 2

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.

Answer 3

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.