alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';create table p_test01(p_date date);insert into p_test01 values(sysdate);
select * from p_test01 where p_date like '2003-04-21 03:10:37';that's ok!
TO 盗版光盘：能让hh为00-23吗？
The TO_CHAR (Convert to Character) function accepts several input data types. Actually, there are three TO_CHAR functions: one for dates, one for numbers and one for MLS Labels. Each accepts a variable of the specified type and then converts it into an equivalent character string. With each type of datatype, there is a format string that can be specified to produce the output format.Basic Syntax of the TO_CHAR function: str1 := TO_CHAR (variable, fmt, nlsparams) Where;str1 This is the VARCHAR2 variable to load the converted value intovariable This is the DATE, NUMBER or MLSLABEL to convertfmt This is the format specifier that is for the type of variable specifiednlsparams This is for DATE and NUMBER types only, it specifies any language specific variables required for the format change. DATE FORMAT SPECIFIERS:Format Model Description CC, SCC Century (S prefixes BC dates with a minus sign) YYYY, SYYYY Year (S prefixes BC dates with a minus sign) IYYY Year based on ISO standard YYY, YY, Y Last three, two or one digits of the year IYY, IY, I Last three, two or one digits of the ISO year Y,YYY (Four Y's with comma) put a comma in the year (1,995) YEAR, SYEAR Year spelled out (S prefixes BC dates with a minus sign) RR Last two digits of year in another century (allows for year 2000) BC, AD BC or AD indicator B.C., A.D. BC or AD indicators with periods Q Numeric quarter of the year (1-4 with Jan-Mar=1) MM 2 number month (Jan = 01) RM Roman numeral month MONTH Name of month spelled out (upper case - month is upper case) MON abbreviated name of month (upper case - month is upper case) WW Week of the year (1-53) IW Week of the year (1-52, 1-53) based on ISO standard W Week of month (1-5) DDD day of year (1-366) (Don't forget leap years) DD day of month (1-31) D day of week (1-7) DAY Name of day (upper case, day is upper case) DY Abbreviated name of day J Julian day (Number of days since January 1, 4712 BC) AM,PM Meridian indicator A.M., P.M. Meridian indicator with periods. HH, HH12 Hour of day (0-12) HH24 Use 24 hour clock for hours (1-24) MI Minute (0-59) SS Second (0-60) SSSSS (five S's) seconds past midnight. (0-86399) None Date must be in the format 'DD-MON-YY';
select * from table_name where trunc(aa)=to_date('2003-04-13','yyyy-mm-dd');
select * from p_test01 where p_date like '2003-04-21 03:10:37';that's ok!
DATE FORMAT SPECIFIERS:Format Model Description
CC, SCC Century (S prefixes BC dates with a minus sign)
YYYY, SYYYY Year (S prefixes BC dates with a minus sign)
IYYY Year based on ISO standard
YYY, YY, Y Last three, two or one digits of the year
IYY, IY, I Last three, two or one digits of the ISO year
Y,YYY (Four Y's with comma) put a comma in the year (1,995)
YEAR, SYEAR Year spelled out (S prefixes BC dates with a minus sign)
RR Last two digits of year in another century (allows for year 2000)
BC, AD BC or AD indicator
B.C., A.D. BC or AD indicators with periods
Q Numeric quarter of the year (1-4 with Jan-Mar=1)
MM 2 number month (Jan = 01)
RM Roman numeral month
MONTH Name of month spelled out (upper case - month is upper case)
MON abbreviated name of month (upper case - month is upper case)
WW Week of the year (1-53)
IW Week of the year (1-52, 1-53) based on ISO standard
W Week of month (1-5)
DDD day of year (1-366) (Don't forget leap years)
DD day of month (1-31)
D day of week (1-7)
DAY Name of day (upper case, day is upper case)
DY Abbreviated name of day
J Julian day (Number of days since January 1, 4712 BC)
AM,PM Meridian indicator
A.M., P.M. Meridian indicator with periods.
HH, HH12 Hour of day (0-12)
HH24 Use 24 hour clock for hours (1-24)
MI Minute (0-59)
SS Second (0-60)
SSSSS (five S's) seconds past midnight. (0-86399)
None Date must be in the format 'DD-MON-YY';