select * from t_TableName where f_ColName = '01-一月 -2030 12:00:00 AM'; 这样就可以吧？
使用to_date()或to_char()函数可以解决.
where field_name = to_date(01-一月 -2030 12:00:00 AM', 'dd-mon-yyyy hh:mi:ss AM');
TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer. The nlsparam has the same purpose in this function as in the TO_CHAR function for date conversion. Do not use the TO_DATE function with a DATE value for the char argument. The first two digits of the returned DATE value can differ from the original char, depending on fmt or the default date format. Note: This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. Date Format SpecifiersFormat 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';For dates, if format specifier is J then the input must be a number.Processing Note: In Oracle7, a space or punctuation character in the format string caused the corresponding character in the date string to be discarded. This caused incorrect dates to be entered into the database since alphanumeric characters were thrown out. As of Oracle8i, an error occurs if an alphanumeric character is found in the date string when a punctuation character or space is found in the format string. For example:
这样就可以吧？
converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer.
The nlsparam has the same purpose in this function as in the TO_CHAR function for date conversion.
Do not use the TO_DATE function with a DATE value for the char argument. The first two digits of the returned DATE value can differ from the original char, depending on fmt or the default date format.
Note: This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. Date Format SpecifiersFormat 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';For dates, if format specifier is J then the input must be a number.Processing Note: In Oracle7, a space or punctuation character in the format string caused the corresponding character in the date string to be discarded. This caused incorrect dates to be entered into the database since alphanumeric characters were thrown out. As of Oracle8i, an error occurs if an alphanumeric character is found in the date string when a punctuation character or space is found in the format string. For example:
TO_CHAR(TO_DATE('0297', 'MM/YY'), 'MM/YY')
Oracle7 result: 02/07
Oracle8 result: ORA-1861