The difference between to char and to date for oracle databases

First, the functions are different.

1. to char: Output the time and date according to the specified format, and get a string instead of the date type.

2. to date: Output the string according to the specified format, and get the date type.

Second, the grammar is different.

1、to char： to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 。

2、to date：to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') 。

Third, the rules are different.

1. to char: As long as it is a date, yyyy, mm, dd add or not add a connector, add any connector.

2. to date: whether there is a connector between yyyy, mm, and dd in the first parameter. If there is, then the second argument must have a connector, whatever it is.

to char is to convert a date or number to a string.

to date is to convert a string to a date type in the database.

Conversion functions. to_char

Use the to char function to process numbers.

to_char(number, 'Format')to_char(salary,’$99,;

Use the to char function to process the date.

to char(date,'format');

to_number

Use the to number function to convert characters to numbers.

to_number(char[, 'Format'])to_date

Use the to date function to convert characters to dates.

to_date(char[, 'Format']) Number format format.

9 represents a number.

0 Force 0 to be displayed

Place a $ symbol.

l Place a floating local currency symbol.

Displays the decimal point.

Displays the thousands indicator.

Date format. Format Control description.

yyyy, yyy, and yy represent 4-digit, 3-digit, and 2-digit numeric years, respectively.

The spelling of the year year.

mm Digital Month.

month month.

Abbreviation for Mon month.

DD Digital Day.

day of the week.

Abbreviation for dy week.

am means morning or afternoon.

HH24, HH12 12-hour or 24-hour clock.

mi minutes.

ss seconds.

The spelling of the sp number.

th ordinal word of the number.

Special Characters" If the special characters.

hh24:mi:ss am 15:43:20 pmdd “of” month 12 of octoberddspth fourteenth

Date in the format '18-May-84'

Differences:

The former is in the year-month-day format: to char(time,'yyyy-mm-dd')

The latter is the year, month, day, hour, minute, second to date('2012-05-11 23:59:59','yyyy-mm-dd hh24:mi:ss')

Composition:

Before we formally learn the SQL language, let's first have a basic understanding of the SQL language and introduce the components of the SQL language

1.An SQL database is a collection of tables that are defined by one or more SQL schemas.

2.An SQL table consists of a set of rows, which are a series (collection) of columns, and each column corresponds to a data item with a row.

3.A table is either a basic table or a view. A base table is a table that is actually stored in a database, while a view is the definition of a table that consists of several base tables or other views.

4.A base table can span one or more storage files, and a storage file can hold one or more base tables. Each storage file corresponds to the previous physical file on external storage.

5.You can use SQL statements to query views and basic tables. From the user's point of view, the view and the basic table are the same, there is no difference, they are all relationships (**).

The user can be either an application or an end user. SQL statements can be embedded in programs in host languages, such as Fortran, Cobol, Pascal, PL I, C, and ADA. SQL users can also be used as stand-alone user interfaces for end users in interactive environments.

The function of the to char function is to set theNumeric typeorDate typeConvert characters to a specific format.

1. Convert the date to the year, month, state, and day format

2. Convert the date into year, month, day, and day of the week format

3. To char decimal system is converted to decimal hexadecimal

Convert strings to date format.

The trunc() function is rightTime typeorFiguresThe time or number type returned by the interception operation.

1. Time

cast() can be used to convert data types.

The main thing is to convert strings to numeric format, which is the opposite of the to char() function.

It is necessary to use the conversion function to convert the string to date type before storing it in the data database, but I feel that there is a problem with your writing, and the time string needs to be enclosed in single quotation marks;

Personally, I recommend not using this way of concatenating strings to concatenate parameter values into statements when writing SQL statements. "The way you dig a pit and then pre-process the object to fill the pit looks beautiful and is not prone to mistakes, for example, I hope my answer will help you.

In oracle's query statement, to date() is a date conversion function.

1.Meaning of Date Format Parameters:

d Day of the week.

The name of the day, padded to 9 characters using spaces.

Days of the month of dd.

Days of the year in DDD.

Shorthand for dy days.

The weeks of the year for the IW ISO standard.

Four-digit year of the IYYY ISO standard.

YYYY 4-digit vintage.

yyy, yy, y the last three of the year, two, one.

hh hours, based on 12 hours.

hh24 hours, calculated as 24 hours.

mi points. ss seconds.

mm month. Abbreviation for mon month.

The full name of the month.

w Weeks of the month.

ww The first week of the year.

Examples: Date and character conversion function usage (to date, to char).

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowtime from dual;Dates are converted to strings.

select to_char(sysdate,'yyyy') as nowyear from dual;Get the time of year.

select to_char(sysdate,'mm') as nowmonth from dual;Get the month of time.

select to_char(sysdate,'dd') as nowday from dual;The day on which the time was obtained.

select to_char(sysdate,'hh24') as nowhour from dual;Get the time of the time.

select to_char(sysdate,'mi') as nowminute from dual;Get the minutes of time.

select to_char(sysdate,'ss') as nowsecond from dual;Gets the seconds of the time.

2.Strings and time are reversed.

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual

select to_char( to_date(222,'j'),'jsp') from dual shows two hundred twenty-two

To date is to convert a string of dates to dates, i.e., varchar to date

to_date('2010-01-01','yyyy-mm-dd');

The first parameter is the data to be converted to a date.

The second is the format to which it is converted.

to_date('2011-1-1','yyyy-mm-dd'To convert this character type to a format type, the value to be converted is the preceding it, followed by the format, and the two sides should be consistent.

to date converts the field to the date type, the first field is the string you entered, and the second field is the format of the date, such as yyyy-mm-dd

to_date（char,'formate'Meaning: Convert char to a specified date format.

For example: to date(.)'79-01-31','yy-mm-dd'That is, January 31, '79.

yy is the year. mm is the month.

dd is the day.

Format the filename field so that it only shows 60 characters.

Oracle database to date() and 24-hour notation and mm minute display: 1. Use "yyyy-mm-dd

hh:mm:ssora

Disadvantages of Format as Format ConversionWhen using Oracle's To Date function to convert dates, many programmers will subconsciously use "yyyy-mm-dd".

hh:mm:ss" format is converted as a slippery format, but it causes an error: "ora

Format ** appears twice".

Example: Knowing to be hungry: select

to_date('2005-01-01

13:14:20','yyyy-mm-dd

hh24:mm:ss')

fromdual;

The original return is that SQL is not case-sensitive, and mm and mm are considered to be the same format**, so Oracle's SQL uses MI instead of minutes.

select

to_date('2005-01-01

13:14:20','yyyy-mm-dd

hh24:mi:ss')

fromdual;

2. In addition, we also need to display the hh24select to be used in the form of 24 hours

to_char(sysdate,'yyyy-mm-dd

hh24:mi:ss')

fromdual;mi is minutes.

select

to_char(sysdate,'yyyy-mm-dd

hh24:mm:ss')

fromdual;mm will display the month.