Project summary 2--oracle Time date format

In Oracle, methods are not as numerous as SQL Server

A conversion method
To_date (): function converts a character type to a date type by a certain format: to_date (' 2004-11-27 13:34:43 ', ' yyyy-mm-dd hh24:mi: ss ')

to _char (): Converted to a string type, such as: To_char (1234.5, ' $9999.9 '), Result: $1234.5

to_number (): Converted to a numeric type, such as: To_number (' 1234.5 '), Result: 1234.5

Oracle's To_date () method functions like the Convert method in SQL Server

Two Oracle date formats

YYYY: Year four-bit representation
Yyy,yy,y: The last three, two, or a bit of the year, the default is the current century
MM: Month number of 01~12
Month: Nine characters in months, and the right is filled with a space
MON: three-character month abbreviation
WW: The week of the year
D: The day of the week
DD: The day ordinal of the month
DDD: The first day of the year
Day: Nine characters full name, right with space
HH, HH12: The first hour of the day, 12 binary notation
HH24: The first hour of the day, the value is 00~23
MI: minutes in an hour
SS: seconds in one minute
SSSS: The number of seconds to go from midnight

Conversion of date format to char type is followed by size comparison, in Java conversion date formatYYYY-MM-DD HH:mm:ssand in SQL because case insensitivity should be writtenYYYY-MM-DD HH24:MI:SS

three Oracle date processing method

oracle the date processing method is also a very rich support date operation Select sysdate+7 from dual;

①Current date and time Sysdate

Select sysdate from dual;

② last day of the month Last_day (sysdate)

Select Last_day (sysdate) from dual;

③ Current date D pushes n months add_months (d,n)

Select add_months (sysdate,2) from dual;

④ The date of the first day of the week from a specific date. Next_day (d, Day_of_week)

Select Next_day (to_date (' 20140404 ', ' YYYYMMDD '), 1) from dual; (1, "Monday ' should be all without testing ')

Four Oracle To_char method

to_ The Char method is more powerful than you think he can implement and SQL Server date function datepart Features of Datename

Select to_char (sysdate, ' Q ') from dual; Quarterly

Select to_char (sysdate, ' WW ') from dual; the first few weeks of WW year.

Select to_char (sysdate, ' W ') from dual; W this month the first few weeks.

Select to_char (sysdate, ' DDD ') from dual; DDD Day of the year, January 1 for 001

Select to_char (sysdate, ' DD ') from dual; DD the day ordinal of the month.

Select to_char (sysdate, ' D ') from dual; The first day of D week.

Select to_char (sysdate, ' HH ') from dual; HH or HH12 12 hours of input

Select to_char (sysdate, ' HH24 ') from dual; HH24 24-hour system

Reprinted from: http://www.cnblogs.com/yydcdut/p/3644007.html

Project summary 2--oracle Time date format