SELECT * FROM tablea WHERE to_date(to_char(bb,'yyyy/mm'),'yyyy/mm') = to_date('2009-01-08','yyyy/mm')
其中bb是一个日期类型得值,例如2002.09,此SQL得目标就是将年月日类型的日期转换为年月,这句SQL应该怎么改,请高手指教
其中bb是一个日期类型得值,例如2002.09,此SQL得目标就是将年月日类型的日期转换为年月,这句SQL应该怎么改,请高手指教
如果BB是字符串,尝试:
SELECT * FROM tablea WHERE bb = to_date('2009-01-08','yyyy.mm')
SELECT * FROM tablea WHERE bb = '2009-01' 如果BB是日期型数据,尝试:SELECT * FROM tablea WHERE to_char(bb,'yyyy-mm' = '2009-01'
不是sql2000中的函数,是 mssql2005中的函数吧?
SELECT * FROM tablea WHERE to_char(bb,'yyyymm') = '200901';
============================================================================
四楼已经说了,用trunc函数就行了。
surname varchar2(200);
firstname varchar2(200);
entityId number(19,0);
tsql varchar2(500);
num number;
numcount number;
cursor CursorPerson is select a.id from entity_item a where a.entity_type = 10;
begin
if(CursorPerson%isopen = false) then
open CursorPerson;
end if;
loop
fetch CursorPerson into entityId;
exit when CursorPerson%notfound;
tsql := 'select a.attribute_value from entity_item_value a where a.entity_id ='|| entityId ||' and a.attribute_id = 3';
execute immediate tsql into surname;
if surname is null then
surname:='';
CONTINUE;
end if;
tsql := 'select a.attribute_value from entity_item_value a where a.entity_id = '||entityId||' and a.attribute_id = 4';
execute immediate tsql into firstname;
if firstname is null then
firstname := '';
CONTINUE;
end if;
end loop;
close CursorPerson;
end CONVERT_PERSONNAME;