天萃荷净
1、oracle求每月第一天和最后一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;
2、sql server对日期的常规处理
SELECT REPLACE(CONVERT(varchar(10),GETDATE(),120),N'-0','-') 短日期格式:yyyy-m-d
SELECT STUFF(STUFF(CONVERT(char(8),GETDATE(),112),5,0,N'年'),8,0,N'月')+N'日' 长日期格式:yyyy年mm月dd日 --A. 方法1
SELECT DATENAME(Year,GETDATE())+N'年'+DATENAME(Month,GETDATE())+N'月'+DATENAME(Day,GETDATE())+N'日' 长日期格式:yyyy年mm月dd日 --A. 方法2
SELECT DATENAME(Year,GETDATE())+N'年'+CAST(DATEPART(Month,GETDATE()) AS varchar)+N'月 '+DATENAME(Day,GETDATE())+N'日' 短日期格式:yyyy年m月d日
SELECT CONVERT(char(11),GETDATE(),120)+CONVERT(char(12),GETDATE(),114) 完整日期+时间格式:yyyy-mm-dd hh:mi:ss:mmm
Select CONVERT(varchar(100), GETDATE(), 8 ): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
3、oracle中400w条记录的表中随机删除20条记录
--使用for
declare
begin
for c in (select * from ( select rowid rd from t2 order by dbms_random.value)m where rownum<=20)
loop
delete from t2 where rowid=c.rd;
end loop;
commit;
end;
--不使用for
declare abc number;
arid rowid;
cursor cursor_sal is
select * from (select rowid rd from t2 order by dbms_random.value)m where rownum<=20;
begin
open cursor_sal;
loop
fetch cursor_sal into arid;
exit when cursor_sal%notfound;
delete from t2 where rowid=arid;
end loop;
close cursor_sal;
commit;
end;
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle SQL语句 开发DBA常用的SQL语句写法汇总