The terms literal and constant value are associated and also describe a addressed data value. For example, "JACK", "BLUE ISLAND", and also "101" are all character literals; 5001 is a numeric literal. Character literals are enclosed in single quotation marks so that can differentiate them from schema object names.

You are watching: In oracle, dates have the format ____.

This area contains these topics:

Many type of SQL statements and also attributes call for you to specify character and numeric literal worths. You have the right to additionally specify literals as component of expressions and also conditions. You can specify character literals with the "text" notation, national character literals through the N"text" notation, and also numeric literals via the integer, or number notation, relying on the context of the literal. The syntactic creates of these notations show up in the sections that follow.

To specify a datetime or interval datakind as a literal, you have to take into account any type of optional precisions consisted of in the datakinds. Instances of specifying datetime and also interval dataforms as literals are gave in the pertinent sections of "Datatypes".

Text Literals

Use the text literal notation to specify values whenever before "string" or shows up in the syntaxes of expressions, conditions, SQL functions, and SQL statements in various other components of this reference. This recommendation provides the terms message literal, character literal, and also string interchangeably. Text, character, and also string literals are always surrounded by single quotation marks. If the syntaxation uses the term char, you have the right to specify either a message literal or another expression that resolves to character information — for instance, the last_name column of the hr.employees table. When char shows up in the syntaxes, the single quotation marks are not supplied.

The syntaxation of message literals is as follows:


Description of the illustration message.gif

wright here N or n specifies the literal making use of the national character set (NCHAR or NVARCHAR2 data). By default, text entered making use of this notation is interpreted into the national character collection by method of the database character set once used by the server. To stop potential loss of data in the time of the message literal conversion to the database character collection, set the setting variable ORA_NCHAR_LITERAL_REPLACE to TRUE. Doing so transparently replaces the n" internally and also preserves the message literal for SQL processing.

See Also: Database Globalization Support Guide for even more indevelopment about N-quoted literals

In the optimal branch of the syntax:

c is any kind of member of the user"s character collection. A single quotation note (") within the literal must be preyielded by an escape character. To represent one single quotation note within a literal, enter 2 single quotation marks.

" " are 2 single quotation marks that begin and also finish message literals.

In the bottom branch of the syntax:

Q or q suggests that the alternative quoting device will certainly be provided. This system allows a vast range of delimiters for the message string.

The outermany " " are two single quotation marks that precede and follow, respectively, the opening and also closing quote_delimiter.

c is any kind of member of the user"s character collection. You can include quotation marks (") in the message literal consisted of of c characters. You can additionally encompass the quote_delimiter, as lengthy as it is not automatically followed by a solitary quotation note.

quote_delimiter is any type of single- or multibyte character other than room, tab, and also rerevolve. The quote_delimiter have the right to be a single quotation note. However, if the quote_delimiter appears in the message literal itself, encertain that it is not instantly followed by a solitary quotation mark.

If the opening quote_delimiter is one of <, , , or (, then the cshedding quote_delimiter must be the equivalent >, , >, or ). In all other instances, the opening and also cshedding quote_delimiter should be the same character.

Text literals have properties of both the CHAR and also VARCHAR2 datatypes:

Within expressions and problems, treats message literals as though they have the dataform CHAR by comparing them utilizing blank-padded comparichild semantics.

A message literal have the right to have actually a maximum length of 4000 bytes.

Here are some valid text literals:

"Hello""""Jackie""s raincoat""09-MAR-98"N"nchar literal"Here are some valid text literals utilizing the different quoting mechanism:

q"!name LIKE "%DBMS_%%"!"q""q"SELECT * FROM employees WHERE last_name = "Smith";"nq"ï Ÿ1234 ï"q""name like "<"""
See Also:

"Blank-Padded and Nonpadded Comparison Semantics"

Numeric Literals

Use numeric literal notation to specify fixed and also floating-suggest numbers.

Integer Literals

You must use the integer notation to specify an integer whenever before integer appears in expressions, conditions, SQL attributes, and also SQL statements described in other parts of this reference.

The syntax of integer is as follows:


Description of the illustration integer.gif

where digit is just one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

An integer deserve to store a maximum of 38 digits of precision.

Here are some valid integers:


NUMBER and also Floating-Point Literals

You must use the number or floating-point notation to specify worths whenever number or n appears in expressions, conditions, SQL attributes, and SQL statements in other parts of this recommendation.

The syntax of number is as follows:


Description of the illustration number.gif


+ or - suggests a positive or negative value. If you omit the authorize, then a positive worth is the default.

digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.

e or E indicates that the number is stated in scientific notation. The digits after the E specify the exponent. The exponent have the right to range from -130 to 125.

f or F indicates that the number is a 32-little binary floating suggest number (of type BINARY_FLOAT).

d or D indicates that the number is a 64-little binary floating point number (of form BINARY_DOUBLE)

If you omit f or F and d or D, then the number is of form NUMBER.

The suffixes f (F) and d (D) are sustained only in floating-allude number literals, not in character strings that are to be converted to NUMBER. That is, if is expecting a NUMBER and it encounters the string "9", then it converts the string to the number 9. However before, if encounters the string "9f", then conversion fails and an error is went back.

A variety of form NUMBER have the right to keep a maximum of 38 digits of precision. If the literal needs even more precision than provided by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then truncates the value. If the range of the literal exceeds the array sustained by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then raises an error.

If you have establimelted a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, then you have to specify numeric literals through "text" notation. In these cases, automatically converts the text literal to a numeric worth.


You cannot usage this notation for floating-suggest number literals.

For instance, if the NLS_NUMERIC_CHARACTERS parameter mentions a decimal character of comma, specify the number 5.123 as follows:

See Also:

ALTER SESSION and also Database Reference

Here are some valid NUMBER literals:

25+6.340.525e-03-1Here are some valid floating-allude number literals:

25f+6.34F0.5d-1DYou deserve to likewise usage the adhering to gave floating-suggest literals in instances wbelow a worth cannot be expressed as a numeric literal:

binary_float_nanA worth of type BINARY_FLOAT for which the problem IS NAN is true


binary_float_infinitySingle-precision positive infinitySELECT COUNT(*) FROM employees WHERE salary
binary_double_nanA worth of type BINARY_DOUBLE for which the condition IS NAN is trueSELECT COUNT(*) FROM employees WHERE TO_BINARY_FLOAT(commission_pct)!= BINARY_FLOAT_NAN;
binary_double_infinityDouble-precision positive infinitySELECT COUNT(*) FROM employees WHERE salary

Datetime Literals Database supports 4 datetime datatypes: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and also TIMESTAMP WITH LOCAL TIME ZONE.

Date LiteralsYou can specify a DATE value as a string literal, or you have the right to transform a character or numeric worth to a day value through the TO_DATE feature. DATE literals are the only case in which Database accepts a TO_DATE expression in area of a string literal.

To specify a DATE worth as a literal, you must usage the Gregorian calendar. You have the right to specify an ANSI literal, as presented in this example:

TO_DATE("98-DEC-25 17:30","YY-MON-DD HH24:MI")The default date format for an DATE worth is specified by the initialization parameter NLS_DATE_FORMAT. This instance day format consists of a two-digit number for the day of the month, an abbreviation of the month name, the last 2 digits of the year, and also a 24-hour time designation. automatically converts character worths that are in the default day format into date values as soon as they are supplied in day expressions.

If you specify a day value without a time component, then the default time is midnight (00:00:00 or 12:00:00 for 24-hour and 12-hour clock time, respectively). If you specify a day value without a day, then the default day is the first day of the present month. DATE columns always contain both the date and also time areas. As such, if you query a DATE column, then you must either specify the moment field in your query or ensure that the time fields in the DATE column are set to midnight. Otherwise, may not rerevolve the query outcomes you mean. You deserve to usage the TRUNC (date) attribute to set the time field to midnight, or you deserve to include a greater-than or less-than problem in the query rather of an equality or inequality condition.

Here are some examples that assume a table my_table via a number column row_num and also a DATE column datecol:

INSERT INTO my_table VALUES (1, SYSDATE);INSERT INTO my_table VALUES (2, TRUNC(SYSDATE));SELECT * FROM my_table; ROW_NUM DATECOL---------- --------- 1 03-OCT-02 2 03-OCT-02SELECT * FROM my_table WHERE datecol = TO_DATE("03-OCT-02","DD-MON-YY"); ROW_NUM DATECOL---------- --------- 2 03-OCT-02SELECT * FROM my_table WHERE datecol > TO_DATE("02-OCT-02", "DD-MON-YY"); ROW_NUM DATECOL---------- --------- 1 03-OCT-02 2 03-OCT-02If you recognize that the time fields of your DATE column are set to midnight, then you deserve to query your DATE column as displayed in the automatically preceding example, or by using the DATE literal:

Use the TO_DATE feature to mask out the time fields:

INSERT INTO my_table VALUES (3, TO_DATE("3-OCT-2002","DD-MON-YYYY"));Use the DATE literal:

INSERT INTO my_table VALUES (4, "03-OCT-02");Use the TRUNC function:

INSERT INTO my_table VALUES (5, TRUNC(SYSDATE));The day function SYSDATE retransforms the existing device date and also time. The function CURRENT_DATE retransforms the current session day. For indevelopment on SYSDATE, the TO_* datetime functions, and the default day format, view "Datetime Functions".

TIMESTAMP LiteralsThe TIMESTAMP datakind stores year, month, day, hour, minute, and also second, and also fractional second worths. When you specify TIMESTAMP as a literal, the fractional_seconds_precision worth have the right to be any kind of number of digits up to 9, as follows:

You can rearea the UTC balance out through the TZR (time zone region) format facet. For example, the complying with example has the same value as the preceding example:

See Also:

"Datetime Expressions" for even more information

If you execute not include the TZD format aspect, and also the datetime worth is ambiguous, then retransforms an error if you have the ERROR_ON_OVERLAP_TIME session parameter collection to TRUE. If that parameter is set to FALSE, then interprets the ambiguous datetime as traditional time in the mentioned area.

TIMESTAMP WITH LOCAL TIME ZONE LiteralsThe TIMESTAMP WITH LOCAL TIME ZONE dataform differs from TIMESTAMP WITH TIME ZONE in that information stored in the database is normalized to the database time zone. The time zone offset is not stored as component of the column information. There is no literal for TIMESTAMP WITH LOCAL TIME ZONE. Rather, you represent worths of this datakind using any type of of the other valid datetime literals. The table that follows mirrors some of the styles you can use to insert a worth right into a TIMESTAMP WITH LOCAL TIME ZONE column, in addition to the matching worth reverted by a query.

Notice that if the value specified does not encompass a time component (either explicitly or implicitly, then the value went back defaults to midnight.

Interval Literals

An interval literal specifies a duration of time. You have the right to specify these differences in regards to years and months, or in terms of days, hours, minutes, and also seconds. Database supports two types of interval literals, YEAR TO MONTH and also DAY TO SECOND. Each type includes a leading field and may contain a trailing field. The leading area specifies the fundamental unit of date or time being measured. The trailing field specifies the smallest increment of the fundamental unit being taken into consideration. For instance, a YEAR TO MONTH interval considers an interval of years to the nearemainder month. A DAY TO MINUTE interval considers an interval of days to the nearemainder minute.

If you have actually day information in numeric develop, then you deserve to use the NUMTOYMINTERVAL or NUMTODSINTERVAL convariation attribute to transform the numeric information into interval worths.

Interval literals are offered mostly through analytic attributes.

See Also:

"Analytic Functions", NUMTODSINTERVAL, NUMTOYMINTERVAL, and also Documents Warereal estate Guide


Specify YEAR TO MONTH interval literals utilizing the following syntax:


Description of the illustration interval_year_to_month.gif


"integer <-integer>" states integer worths for the leading and optional trailing area of the literal. If the leading field is YEAR and the trailing field is MONTH, then the array of integer values for the month field is 0 to 11.

precision is the maximum number of digits in the leading area. The valid array of the leading area precision is 0 to 9 and its default worth is 2.

Restriction on the Leading FieldIf you specify a trailing field, it have to be much less significant than the leading field. For instance, INTERVAL "0-1" MONTH TO YEAR is not valid.

The complying with INTERVAL YEAR TO MONTH literal indicates an interval of 123 years, 2 months:

INTERVAL "123-2" YEAR(3) TO MONTHInstances of the other creates of the literal follow, including some abbreviated versions:

Form of Interval LiteralInterpretation
INTERVAL "123-2" YEAR(3) TO MONTHAn interval of 123 years, 2 months. You need to specify the leading area precision if it is greater than the default of 2 digits.
INTERVAL "123" YEAR(3)An interval of 123 years 0 months.
INTERVAL "300" MONTH(3)An interval of 300 months.
INTERVAL "4" YEARMaps to INTERVAL "4-0" YEAR TO MONTH and indicates 4 years.
INTERVAL "50" MONTHMaps to INTERVAL "4-2" YEAR TO MONTH and also suggests 50 months or 4 years 2 months.
INTERVAL "123" YEARRetransforms an error, bereason the default precision is 2, and "123" has 3 digits.

You have the right to include or subtract one INTERVAL YEAR TO MONTH literal to or from one more to yield another INTERVAL YEAR TO MONTH literal. For example:



Specify DAY TO SECOND interval literals making use of the following syntax:


Description of the illustration interval_day_to_second.gif


integer specifies the variety of days. If this worth consists of more digits than the number specified by the leading precision, then returns an error.

leading_precision is the variety of digits in the leading field. Accepted values are 0 to 9. The default is 2.

fractional_seconds_precision is the variety of digits in the fractional component of the SECOND datetime field. Accepted worths are 1 to 9. The default is 6.

Restriction on the Leading Field:If you specify a trailing field, it need to be less substantial than the leading field. For example, INTERVAL MINUTE TO DAY is not valid. As an outcome of this restriction, if SECOND is the leading area, the interval literal cannot have any kind of trailing area.

The valid array of worths for the trailing area are as follows:

HOUR: 0 to 23

MINUTE: 0 to 59

SECOND: 0 to 59.999999999

Instances of the various forms of INTERVAL DAY TO SECOND literals follow, including some abbreviated versions:

Form of Interval LiteralInterpretation
INTERVAL "4 5:12:10.222" DAY TO SECOND(3)4 days, 5 hrs, 12 minutes, 10 seconds, and 222 thousandths of a second.
INTERVAL "4 5:12" DAY TO MINUTE4 days, 5 hrs and also 12 minutes.
INTERVAL "400 5" DAY(3) TO HOUR400 days 5 hrs.
INTERVAL "400" DAY(3)400 days.
INTERVAL "11:12:10.2222222" HOUR TO SECOND(7)11 hrs, 12 minutes, and also 10.2222222 seconds.
INTERVAL "11:20" HOUR TO MINUTE11 hrs and also 20 minutes.
INTERVAL "10" HOUR10 hrs.
INTERVAL "10:22" MINUTE TO SECOND10 minutes 22 secs.
INTERVAL "10" MINUTE10 minutes.
INTERVAL "4" DAY4 days.
INTERVAL "25" HOUR25 hrs.
INTERVAL "40" MINUTE40 minutes.
INTERVAL "120" HOUR(3)120 hrs.
INTERVAL "30.12345" SECOND(2,4)30.1235 seconds. The fractional second "12345" is rounded to "1235" bereason the precision is 4.

See more: The Poop That Took A Pee Book, The Poop That Took A Pee: Southpark

You deserve to include or subtract one DAY TO SECOND interval literal from one more DAY TO SECOND literal. For example.