Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.)
Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.)
CURRENT_DATE returns the current date and time as per the session time zone. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE, but it includes additional details such as fractional seconds and the time zone information associated with the session.
C & F is correct
C & F are correct SQL> SQL> SELECT sessiontimezone FROM DUAL; SESSIONTIMEZONE --------------------------------------------------------------------------- +10:00 SQL> select current_date from dual; CURRENT_DATE ----------------------------- 31-MAY-2023 02:24:46 SQL> select CURRENT_TIMESTAMP from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 31-MAY-23 02.25.10.789980 AM +10:00 SQL> select SYSDATE from dual; SYSDATE ----------------------------- 30-MAY-2023 22:25:23 SQL> select CURRENT_DATE from dual; CURRENT_DATE ----------------------------- 31-MAY-2023 02:25:41 SQL> select CURRENT_TIMESTAMP from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 31-MAY-23 02.25.58.563965 AM +10:00 SQL> select sysdate from v$database; SYSDATE ----------------------------- 30-MAY-2023 22:26:58 SQL>
I think A and C are correct. SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SQL> select sysdate,current_date,current_timestamp from dual; SYSDATE CURRENT_DATE CURRENT_TIMESTAMP -------------------- -------------------- --------------------------------------------------------------------------- 28-SEP-2021 14:32:48 28-SEP-2021 14:32:48 28-SEP-21 02.32.48.201457 PM +03:00 https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions036.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions037.htm
AC is correct
A cannot be correct. Becasue your DB server and your session are in same region, current_date and sysdate are same. If you connect to your DB at another timezone, that command return another result. So....it can be same, but not always do.
so corrects are C , F
I think C and E are correct, CURRENT_TIMESTAMP shows more content than current_date
but not in smae time zone
C,F is correct
SQL> set serveroutput on SQL> declare 2 ld date; 3 begin 4 ld:=sysdate; 5 dbms_output.put_line(ld); 6 end; 7 / 08-AUG-2021 15:12:35
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; select current_timestamp from dual; select current_date from dual; select sysdate from dual; 08-AUG-21 02.00.13.303335 AM US/PACIFIC 08-AUG-2021 02:00:15 08-AUG-2021 09:00:17 Now check the answers!!!!
A - false; it is the same only in case database server operating system uses the same time zone as the operating system of the database client (session time zone) B - false; can be queried in any table, e.g. SELECT emp_no,ename,salary,mgr_no,sysdate FROM emp; C - true; SQL> select sys_extract_utc(systimestamp) from dual; SYS_EXTRACT_UTC(SYSTIMESTAMP) --------------------------------------------------------------------------- 11.09.23 13:58:24,316896 SQL> SELECT SESSIONTIMEZONE FROM DUAL; SESSIONTIMEZONE --------------------------------------------------------------------------- +02:00 SQL> SELECT current_date FROM dual; CURRENT_DATE -------------------- 11-SEP-2023 15:58:41 D - false; sysdate can be used independently of the setting of the NLS_DATE_FORMAT E - false; sydate returns date and time of the operating system of the database server, current_date returns date and time in the session time zone (i.e. in the time zone of the client) F - true; both variables returns current time and date set in the session time zone
CURRENT_DATE and CURRENT_TIMESTAMP are tied to session time zone SYSDATE is tied to database server date and time. It can be used querying any table, not just dual, no matter the session nls_date_format chosen
F cannot be correct oracle@localhost ~]$ cat date_format.sql alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' ; select current_timestamp from dual ; select current_date from dual ; [oracle@localhost ~]$ sqlplus system/oracle @date_format.sql SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 6 04:41:16 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Wed Apr 06 2022 04:41:01 -04:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Session altered. CURRENT_TIMESTAMP --------------------------------------------------------------------------- 06-APR-22 04.41.16.562674 AM -04:00 CURRENT_DATE -------------------- 06-APR-2022 04:41:16
F cannot be correct: [oracle@localhost ~]$ cat date_format.sql alter session set NLS_DATE_FORMAT is set to 'DD-MON-YYYY HH24:MI:SS' ; select current_timestamp from dual ; select current_date from dual ; [oracle@localhost ~]$ sqlplus system/oracle @date_format.sql SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 6 04:39:17 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Wed Apr 06 2022 04:38:02 -04:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 alter session set NLS_DATE_FORMAT is set to 'DD-MON-YYYY HH24:MI:SS' * ERROR at line 1: ORA-00927: missing equal sign CURRENT_TIMESTAMP --------------------------------------------------------------------------- 06-APR-22 04.39.18.153607 AM -04:00 CURRENT_D --------- 06-APR-22
Current_date is session specific, Current_timestamp is also session specific with zone and sysdate is operating system date . So only left with Valid choice B and C
oops my mistake . C and F are only valid choices. So C and F are correct
B and C are correct!
the correct answer is should be A and F
sorry CF is the correct one
E is also wrong becuase sysdate Displays only date
if you read the question "where nls_date_format is set to ...." I have done this and this is the result: SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Sesion modificada. SQL> select sysdate from dual; SYSDATE -------------------- 17-AGO-2020 17:59:34 SQL> select current_date from dual; CURRENT_DATE -------------------- 17-AGO-2020 17:59:53 maybe E is correct
A is wrong becuase sysdate Displays only date but not the time.
SYSDATE also shows time if you change the date display format to include it. However, A is wrong because SYSDATE shows database resident OS date&time while CURRENT_TIMESTAMP shows session date&time. Different time zones can affect the result.