什么意思? 比如我有一个SQL select * from table where ..... where后的条件就是让一个叫date1的字段(其数据类型为date)的年份为2006。 这个where如何写
select * from table where to_char(col,'yyyy')='2006' 字段col为日期型
年 select to_char(sysdate,'yyyy') from dual 月 select to_char(sysdate,'mm') from dual 日 select to_char(sysdate,'dd') from dual 最后一天 select to_char(last_day(sysdate),'yyyy.mm.dd') from dual; 今天、明天 select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
select * from table where to_char(data1,'yyyy')='2006'
to_char(),to_date()这2个转换函数是经常使用的,要多用用啊。
语法: TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ]) 变量: d:有效的日期变量。 X:在单引号中指定的字符型变量,它表示返回的日期类型。是可选项。 nls_parm:指定返回的年、月和日的语言。该参数是可选的。缺省的,它将采用缺省的日期语言。这个参数可以指定为:‘NLS_DATE_LANGUAGE = language’。ExamplesThe following example uses this table:CREATE TABLE date_tab ( ts_col TIMESTAMP, tsltz_col TIMESTAMP WITH LOCAL TIME ZONE, tstz_col TIMESTAMP WITH TIME ZONE);ALTER SESSION SET TIME_ZONE = '-8:00'; INSERT INTO date_tab VALUES ( TIMESTAMP'1999-12-01 10:00:00', TIMESTAMP'1999-12-01 10:00:00', TIMESTAMP'1999-12-01 10:00:00'); INSERT INTO date_tab VALUES ( TIMESTAMP'1999-12-02 10:00:00 -8:00', TIMESTAMP'1999-12-02 10:00:00 -8:00', TIMESTAMP'1999-12-02 10:00:00 -8:00');SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'), TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') FROM date_tab;TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI: ------------------------------ ------------------------------------- 01-DEC-1999 10:00:00 01-DEC-1999 10:00:00.000000 -08:00 02-DEC-1999 10:00:00 02-DEC-1999 10:00:00.000000 -08:00SELECT SESSIONTIMEZONE, TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') FROM date_tab;SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY --------------- ------------------------------ -08:00 01-DEC-1999 10:00:00.000000 -08:00 02-DEC-1999 10:00:00.000000ALTER SESSION SET TIME_ZONE = '-5:00'; SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'), TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') FROM date_tab;TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI: ------------------------------ ------------------------------------- 01-DEC-1999 10:00:00.000000 01-DEC-1999 10:00:00.000000 -08:00 02-DEC-1999 10:00:00.000000 02-DEC-1999 10:00:00.000000 -08:00SELECT SESSIONTIMEZONE, TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') FROM date_tab;SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY ------------------------- ------------------------------ -05:00 01-DEC-1999 13:00:00.000000 -05:00 02-DEC-1999 13:00:00.000000--------------------------------------------------------------------------------
如果我想模糊查询,就是必须用到like,如何去做呢?
模糊查询可以使用like和instr ... where column like '%v_value%';... where instr(column,'v_value') > 0;
比如我有一个SQL
select * from table where .....
where后的条件就是让一个叫date1的字段(其数据类型为date)的年份为2006。
这个where如何写
字段col为日期型
select to_char(sysdate,'yyyy') from dual
月
select to_char(sysdate,'mm') from dual
日
select to_char(sysdate,'dd') from dual
最后一天
select to_char(last_day(sysdate),'yyyy.mm.dd') from dual;
今天、明天
select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
语法:
TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])
变量:
d:有效的日期变量。
X:在单引号中指定的字符型变量,它表示返回的日期类型。是可选项。
nls_parm:指定返回的年、月和日的语言。该参数是可选的。缺省的,它将采用缺省的日期语言。这个参数可以指定为:‘NLS_DATE_LANGUAGE = language’。ExamplesThe following example uses this table:CREATE TABLE date_tab (
ts_col TIMESTAMP,
tsltz_col TIMESTAMP WITH LOCAL TIME ZONE,
tstz_col TIMESTAMP WITH TIME ZONE);ALTER SESSION SET TIME_ZONE = '-8:00';
INSERT INTO date_tab VALUES (
TIMESTAMP'1999-12-01 10:00:00',
TIMESTAMP'1999-12-01 10:00:00',
TIMESTAMP'1999-12-01 10:00:00');
INSERT INTO date_tab VALUES (
TIMESTAMP'1999-12-02 10:00:00 -8:00',
TIMESTAMP'1999-12-02 10:00:00 -8:00',
TIMESTAMP'1999-12-02 10:00:00 -8:00');SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'),
TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM')
FROM date_tab;TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI:
------------------------------ -------------------------------------
01-DEC-1999 10:00:00 01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00 02-DEC-1999 10:00:00.000000 -08:00SELECT SESSIONTIMEZONE,
TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF')
FROM date_tab;SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY
--------------- ------------------------------
-08:00 01-DEC-1999 10:00:00.000000
-08:00 02-DEC-1999 10:00:00.000000ALTER SESSION SET TIME_ZONE = '-5:00';
SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'),
TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM')
FROM date_tab;TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI:
------------------------------ -------------------------------------
01-DEC-1999 10:00:00.000000 01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00.000000 02-DEC-1999 10:00:00.000000 -08:00SELECT SESSIONTIMEZONE,
TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF')
FROM date_tab;SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY
------------------------- ------------------------------
-05:00 01-DEC-1999 13:00:00.000000
-05:00 02-DEC-1999 13:00:00.000000--------------------------------------------------------------------------------
...
where column like '%v_value%';...
where instr(column,'v_value') > 0;
用类似where to_char(data1,'yyyy')like'20__'查出二十一世纪的年份(这个语法错误)
where to_char(data1,'yyyy') like '2%';
where to_char(data1,'yyyy') like '20%';