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

1
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

July 9, 2003The FM removes leading zeros

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

07-NOV-03 as a date type

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

05-JUN-03 as a date type

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

2000 as a character data type

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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

A

00 as a character data type

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

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

A

“all values for the field ““Day Type”” which will have either ‘Weekday’ or ‘Weekend’ as a return for the column ““Day Type”””

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

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

A

“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.”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

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

A

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’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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

A

The next Monday after today.4-Nov-98

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

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

A

31-Oct-98The last day of the Month

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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;

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

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

A

a numeric value which is the difference between the 2 dates

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

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

A

a numeric value which is the difference between the 2 dates

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

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

A

errorcannot subtract a string from a string

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

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

A

MONTHS_BETWEEN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

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

A

NEXT_DAY(sysdate,’FRIDAY’)

38
Q

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

A

LAST_DATE(sysdate)

39
Q

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

A

ADD_MONTHS(sysdate,2)

40
Q

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

A

01-AUG-03

41
Q

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

A

01-SEP-03

42
Q

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

A

01-JAN-03

43
Q

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

A

WW

44
Q

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

A

W

45
Q

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

A

DDD

46
Q

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

A

DD

47
Q

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

A

D

48
Q

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

A

MM

49
Q

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

A

MON

50
Q

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

A

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

51
Q

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

A

HH24

52
Q

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

A

MI

53
Q

“What numerical format element will return ““4th””?”

A

DDTH

54
Q

“What numerical format element will return ““FOUR””?”

A

DDSP

55
Q

“What numerical format element will return ““FOURTH””?”

A

DDSPTH

56
Q

“What symbol will return ““Fourth””?”

A

fmDdspthfm removes preceding spaces

57
Q

What format element will display the local currency symbol?

A

L

58
Q

What format element will represents a number?

A

9

59
Q

What format element is needed to represent a leading zero?

A

0

60
Q

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

A

90$L. (Period), (Comma)

61
Q

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

A

fx

62
Q

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

A

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

63
Q

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

A

-12.033312 months and 1 day

64
Q

What data type is returned from the MONTHS_BETWEEN function?

A

numeric value

65
Q

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

A

14-May-2007subtract 2 months and 1 day

66
Q

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

A

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

67
Q

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

A

date data types

68
Q

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

A

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

69
Q

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

A

YYYY - full year in numbersYEAR - year spelled out

70
Q

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

A

DAY - MONDAYDay - Mondayday - monday

71
Q

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

A

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

72
Q

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

A

Format model modifiers

73
Q

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

A

Format model elements

74
Q

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

A

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

75
Q

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

A

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

76
Q

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

A

April 03without trailing spaces

77
Q

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

A

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

78
Q

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

A

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

79
Q

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

A

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

80
Q

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;

A

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.

81
Q

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

A

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

82
Q

Are dates format sensitive when sorted?

A

Yes.

83
Q

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

A

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

84
Q

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

A

INTERVAL YEAR TO MONTH

85
Q

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

A

INTERVAL DAY TO SECOND

86
Q

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

A

-12.03333

87
Q

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

A

they must be date data types

88
Q

What data type is returned from the ADD_MONTHS function?

A

date data type

89
Q

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

A

14-MAY-2000subtract 2 months and 1 day

90
Q

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;

A

Nothing the statement will return the same results.

91
Q

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

A

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