Oracle__6. Oracle 1Z0-051 Exam - Date Functions Flashcards by Deleted Deleted

If today is July 9, 2003, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘yyyy/MM/dd’) FROM dual

2003/07/09The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)

How well did you know this?

Not at all

Perfectly

If today is July 9, 2003, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘Month DD, YYYY’) FROM dual

July 09, 2003The TO_CHAR function will have a leading space added when a format model is used for the sign (+/-)

How well did you know this?

Not at all

Perfectly

If today is July 9, 2003, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘FMMonth DD, YYYY’) FROM dual

July 9, 2003The FM removes leading zeros

How well did you know this?

Not at all

Perfectly

If today is July 9, 2003, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘FMMonth DDth, yyyy’) FROM dual

July 9TH, 2003Notice July is capitalized and TH is all capital letters. The upper and lower case in format strings determines the case of the results.

How well did you know this?

Not at all

Perfectly

If today is July 9, 2003, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘FMMON DDth, YYYY’) FROM dual

JUL 9TH, 2003Notice both JUL and TH are all capital letters. The upper and lower case in format strings determines the case of the results.

How well did you know this?

Not at all

Perfectly

If today is July 9, 2003, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘FMMon ddth, YYYY’) FROM dual

Jul 9th, 2003Notice July is capitalized and th is all small letters. The upper and lower case in format strings determines the case of the results.

How well did you know this?

Not at all

Perfectly

If today is Monday, what is returned from the following statement? SELECT TO_CHAR( sysdate,’DAY’) FROM Dual

MONDAYThe upper and lower case in format strings determines the case of the results.

How well did you know this?

Not at all

Perfectly

If today is Monday, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘Day’) FROM Dual

MondayThe upper and lower case in format strings determines the case of the results.

How well did you know this?

Not at all

Perfectly

If today is Monday, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘day’) FROM Dual

mondayThe upper and lower case in format strings determines the case of the results.

How well did you know this?

Not at all

Perfectly

If it is 1:10 pm, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘HH:mm:ss A.M.’) FROM dual

1:10:00 P.M.Notice periods in PM because there were periods in A.M.

How well did you know this?

Not at all

Perfectly

If it is 1:10 pm, what is returned from the following statement? SELECT TO_CHAR( sysdate, ‘HH24:mm:ss AM’) FROM dual

13:10:00 PMNotice no periods in PM because there were no periods in AM

How well did you know this?

Not at all

Perfectly

If today is July 4, 2003, what is returned from the following statement? SELECT TO_DATE( ‘2003/07/09’, ‘yyyy/mm/dd’) FROM dual

09-Jul-99 The value returned is a date type and not a string. The default date type format is DD-Mon-RRNote: the data type format of the value must match the format. It does not care what today is.

How well did you know this?

Not at all

Perfectly

If today is July 4, 2003, what is returned from the following statement? SELECT TO_CHAR( ‘070903’, ‘mmddyy’) FROM dual

jul0903The upper and lower case in format strings determines the case of the results. It does not care what today is.

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT LAST_DAY( ‘2003/02/03’, ‘yyyy/mm/dd’) FROM dual

28-feb-03 as a date typeThe last day of the month including leap years.The upper and lower case in format strings determines the case of the results.

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT NEXT_DAY( ‘01-AUG-03’, ‘FRIDAY’) FROM dual

returns the date of the next Friday after 1-Aug-03 as a date typeThe day values for the format include SUNDAY, MONDAY, TUESDAY, WEDNESDAY,THURSDAY, FRIDAY and SATURDAY

How well did you know this?

Not at all

Perfectly

If today is FRIDAY, what is returned from the following statement? SELECT NEXT_DAY(sysdate, ‘FRIDAY’) FROM dual

The date value of the next Friday after today.Saturday a week after tomorrow.

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT ADD_MONTHS( ‘07-AUG-03’,3) FROM dual

07-NOV-03 as a date type

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT ADD_MONTHS( ‘05-AUG-03’,-2) FROM dual

05-JUN-03 as a date type

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT MONTHS_BETWEEN( TO_DATE( ‘2003/01/01’, ‘yyyy/mm/dd’), TO_DATE( ‘03-14-2003’, ‘mm-dd-yyyy’)) FROM dual

-2.41934if the same date is used for both parameters the result is 0

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT ROUND( ‘22-AUG-03’, ‘YEAR’) FROM dual

‘01-JAN-04’rounds to the first of the nearest year

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT ROUND( ‘22-AUG-03’, ‘MONTH’) FROM dual

‘01-SEP-03’rounds to the first of the nearest month

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT TRUNC( TO_DATE( ‘22-AUG-03’, ‘YEAR’)) FROM dual

‘01-JAN-03’Truncates to the first day of the year

How well did you know this?

Not at all

Perfectly

What is returned from the following statement? SELECT TRUNC( ‘22-AUG-03’, ‘MONTH’) FROM dual

‘01-AUG-03’Truncates to the first day of the month

How well did you know this?

Not at all

Perfectly

What is returned from this SQL statement if the value for hire_date is 10-JAN-00? SELECT TO_CHAR(hire_date,’yyyy’) FROM dual

2000 as a character data type

How well did you know this?

Not at all

Perfectly

What is returned from this SQL statement if the value for hire_date is 10-JAN-00? SELECT TO_CHAR(hire_date,'rr') FROM dual

00 as a character data type

What is returned from this SQL statement if the field value for hire_date is 01-JAN-00? SELECT TO_CHAR(hire_date,'mon dd yy') FROM dual

jan 01 00 as a character data typeNotice lower case and leading zeros

What is returned from the following statement? SELECT trans_date, CASE TRIM(TO_CHAR(trans_date,'DAY')) in ('SATURDAY','SUNDAY') THEN 'Weekend' ELSE 'Weekday' END 'Day Type' FROM transactions

"all values for the field ""Day Type"" which will have either 'Weekday' or 'Weekend' as a return for the column ""Day Type"""

What is returned from following statement? SELECT trans_date, CASE TRIM(TO_CHAR(trans_date,'DAY')) BETWEEN 'MONDAY' AND 'FRIDAY' 'Weekday' ELSE 'Weekend' END 'Day Type' FROM transactions

"all values for the field ""Day Type"" will be 'Weekend' because no values will fall between 'MONDAY' and 'FRIDAY'The first value of a BETWEEN must be the lower value."

What is returned from following statement? SELECT trans_date, CASE TRIM(TO_CHAR(trans_date,'DAY')) BETWEEN 'MONDAY' AND 'THURSDAY' 'Weekday' ELSE 'Weekend' END 'Day Type' FROM transactions

Values for days on MONDAY, TUESDAY, THURSDAY, SATURDAY and SUNDAY will be weekday while the rest will be weekend.BETWEEN does a string compare from words beginning with 'MO' thru 'TH'

If today is Monday 28-OCT-98, what is returned from the following statement? SELECT NEXT_DAY(SYSDATE,'MON') FROM dual

The next Monday after today.4-Nov-98

If today is Monday 28-OCT-98, what is returned from the following statement? SELECT LAST_DAY(SYSDATE) FROM DUAL

31-Oct-98The last day of the Month

What is the difference between the return values of the following 2 statements? SELECT TO_CHAR('28-OCT-2009','fmMonth') FROM dual; SELECT TO_CHAR('28-OCT-2009','Month') FROM dual;

The fmMonth will trim trailing spaces, because Month will have 2 trailing spaces.The TO_CHAR with a month has 9 character to hold the longest month name plus a trailing space

What is returned from the following statement? SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM dual

a numeric value which is the difference between the 2 dates

What is returned from the following statement? SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM dual

a numeric value which is the difference between the 2 dates

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY') - '01-JAN-2007' FROM dual

errorcannot subtract a string from a string

What date function accepts a date data type but returns a numeric value?

MONTHS_BETWEEN

What is the syntax of a function that will return the next Friday after the current date?

NEXT_DAY(sysdate,'FRIDAY')

What is the syntax of a function that will return the last day of the current month?

LAST_DATE(sysdate)

What is the syntax of a function that will add 2 months to the current date?

ADD_MONTHS(sysdate,2)

What is returned from ROUND('15-AUG-03','MONTH')

01-AUG-03

What is returned from ROUND('16-AUG-03','MONTH')

01-SEP-03

What is returned from TRUNC('16-Aug-03','YEAR')

01-JAN-03

What date format element will give you the week of the year?

What date format element will give you the week of the month?

What date format element will give you the Day of the year?

DDD

What date format element will give you the day of the Month?

What date format element will give you the day of the Week?

What date format element will give you the 2 digit numerical month of the year?

What date format element will give you the 3 letter abbreviation for the month?

MON

What date format element will give you a 9 character string of a month with trailing spaces?

MONTHMONTH always return a 9 character stringDecember has one trailing spaceMAY as 6 trailing spaces

What date format element will represent the hour in values from 0 to 24?

HH24

What date format element will represent the minutes from 0 to 59?

"What numerical format element will return ""4th""?"

DDTH

"What numerical format element will return ""FOUR""?"

DDSP

"What numerical format element will return ""FOURTH""?"

DDSPTH

"What symbol will return ""Fourth""?"

fmDdspthfm removes preceding spaces

What format element will display the local currency symbol?

What format element will represents a number?

What format element is needed to represent a leading zero?

What format symbols are allow when using the TO_CHAR function to represent numeric values?

90$L. (Period), (Comma)

What modifier specifies the exact match for the character argument and date format model of a TO_DATE function?

Why does this create an error TO_DATE('May 24, 1999', 'fxMonth DD, YYYY')

the fx means exact match. There are too many spaces between May and 24.

What is returned from the following statement? SELECT MONTHS_BETWEEN(TO_DATE('10-JUL-2000'),TO_DATE('09-MAY-1999') FROM Dual

-12.033312 months and 1 day

What data type is returned from the MONTHS_BETWEEN function?

numeric value

What is returned from the following statement? SELECT ADD_MONTHS('15-JUL-2007',-2.0333) FROM dual

14-May-2007subtract 2 months and 1 day

What is returned from each of these elements of the date format model? DY DAY DD

DY - three-letter abbreviation of the day of the weekDAY - full name of the day of the weekDD - numeric day of the month

What data types must be in the 2 arguments for MONTHS_BETWEEN function?

date data types

What is returned from each of these elements of the date format model? MM MON MONTH

MM - 2 digit value of the monthMON - three-letter abbreviation of the monthMONTH - full name of the month

What is returned from each of these elements of the date format model? YYYY YEAR

YYYY - full year in numbersYEAR - year spelled out

Assuming today is Monday, what is returned from each of these element of the date format model? DAY Day day

DAY - MONDAYDay - Mondayday - monday

What is returned from the following statement? TO_CHAR (TO_DATE('0297','MM/YY'), 'MM/YY')

An errorThe TO_DATE format has a slash but the numbers do not. The format of the character string must match

The characters FM and FX used in Format Models are termed?

Format model modifiers

The characters DD, DDD, YYYY, YEAR, MONTH, RR used in Format Models are termed?

Format model elements

The characters TH and SP which can be added to the end of a format model element are termed?

Format model suffixIf the number is 9 what will be the result from these format models?DD - 09DDTH - 09THDDSP - NINE

What is returned from the following statement? SELECT LENGTH(TO_CHAR('04/01/20013, 'Month yy' ) FROM dual

April 03With 4 trailing spaces, The 'Month' format element always has 9 characters

What is returned from the following statement? SELECT LENGTH(TO_CHAR('04/01/20013, 'FmMonth yy') FROM dual

April 03without trailing spaces

What is the difference between these two formats? TO_CHAR('01-JAN-1998','FXDD-MON-YYYY') TO_CHAR('1-JAN-1998','FXDD-MON-YYYY')

The FX format model modifier requires the exact match.The second one fails because the day of 1 does not match the format DD

If today is Aug 4, 1997, what is returned from the following statement? SELECT UPPER (sysdate) FROM dual

04-aug-97The month is in lower caseA date type can be in a character conversion function

What is returned from the following statement? SELECT SUBSTR(sysdate,10,7) FROM dual;

NullSysdate is not textSELECT SUBSTR(TO_DATE(sysdate),10,7) FROM dual will not cause an error.

Which of the following will return the date and time in fractional seconds? SELECT TO_CHAR(sysdate, 'HH24:MI:SS.FF3') FROM dual; SELECT TO_CHAR(sysdate, 'HH24:MI:SS.FF') FROM dual;

Both will return fractional seconds.The FF3 will return seconds to 3 decimal places.The FF will return seconds to decimal place defined by the default of the date type.

The TO_DATE function converts what date type to a date data type?

a CHAR or VARCHAR2, but must be in a date format 'dd-MON-rr' if there is no format element.

Are dates format sensitive when sorted?

Yes.

In this expression what does the fm do? to_Date('01-01-1999','fmDD-MM-YY')

The fm suppresses zeros.The result would be: 1-1-99

What data type would you expect if you see this data in a column? +001-02

INTERVAL YEAR TO MONTH

What data type would you expect if you see this data in a column? +002 10:20:30.456

INTERVAL DAY TO SECOND

What is returned from this statement? SELECT MONTHS_BETWEEN( TO_DATE('10-OCT-2000'),TO_DATE('09-OCT-1999) FROM dual

-12.03333

What data type must be the 2 arguments in the MONTHS_BETWEEN function?

they must be date data types

What data type is returned from the ADD_MONTHS function?

date data type

What is returned from this statement? SELECT ADD_MONTHS(TO_DATE('15-JUL-2000'),-2.0333)

14-MAY-2000subtract 2 months and 1 day

What is the different between the returns of these 2 statements? SELECT TO_DATE('01/JANUARY/2007') FROM DUAL; SELECT TO_DATE('01-JANUARY-2007') FROM DUAL;

Nothing the statement will return the same results.

Will this statement cause and error? SELECT promo FROM promotions WHERE promo_date > '01-JAN-01'

NoThe date format matches the default date format DD-MON-RR