实现功能
输入"2008-12-21 12:20:20"
输出"2008-12-21 12:00:00"我写的有问题
CREATE FUNCTION getdatehour(IDE date)
RETURN date as OED date;
BEGIN
OED:=cast(to_char(IDE,'YY')+'-'+to_char(IDE,'MM')+'-'+to_char(IDE,'DD')+' '+to_char(IDE,'HH24')+':00:00' as date);
return ODE;
END;谁能给我个正确的函数啊
输入"2008-12-21 12:20:20"
输出"2008-12-21 12:00:00"我写的有问题
CREATE FUNCTION getdatehour(IDE date)
RETURN date as OED date;
BEGIN
OED:=cast(to_char(IDE,'YY')+'-'+to_char(IDE,'MM')+'-'+to_char(IDE,'DD')+' '+to_char(IDE,'HH24')+':00:00' as date);
return ODE;
END;谁能给我个正确的函数啊
解决方案 »
- 带子查询的SQL语句怎么实现for update
- 无法加载 DLL“OraOps10.dll”: 找不到指定的程序。 (异常来自 HRESULT:0x8007007F)。
- expdp导出的问题
- 大家好,请问大家有没有oracle教程 谢谢
- 登录问题
- 如何改变创建数据库的文件存储的默认位置
- 谁能帮忙解决一下关于ora-00942和ora-02063的错误啊
- oracle8.05+develop2000开发的程序在oracle9i+oracle6i下为什么不能运行?
- 提个问题,在线等待
- sqlj资料!
- 关于sqlloader log日志问题
- 【愁死了】ORA-03115错误,大伙们来帮帮忙啊,弄2天了,快被搞疯掉了~!!!
RETURN date as OED date;
BEGIN
OED:=substr(IDE,1,14)||00:00;
return ODE;
END;
CREATE OR REPLACE FUNCTION getdatehour(IDE date)
RETURN date
is
OED date;
BEGIN
OED:=trunc(IDE,'hh');
return OED;
END;select getdatehour(sysdate) from dual;
*
ERROR 1 :
ORA-00922:
SP2-0552: "00"
CREATE or replace FUNCTION getdatehour(IDE date)
RETURN date as
ODE date;
BEGIN
ODE := to_date(to_char(ide, 'yyyy-mm-dd hh24') || '00:00','yyyy-mm-dd hh24:mi:ss');
return ODE;
END;测试代码:declare
d date := sysdate;
begin
d := getdatehour(d);
dbms_output.put_line(to_char(d,'yyyy-mm-dd hh24:mi:ss'));
end;结果
2010-05-13 17:00:00
1:
SQL> edi
已写入 file afiedt.buf 1 select to_char(trunc(to_date('2008-12-21 12:20:20','yyyy-mm-dd hh24:mi:ss'),'hh'),
2* 'yyyy-mm-dd hh24:mi:ss') from dual
SQL> /TO_CHAR(TRUNC(TO_DA
-------------------
2008-12-21 12:00:002:
SQL> edi
已写入 file afiedt.buf 1 create or replace function f_date(dt date)
2 return varchar2
3 as
4 dt1 varchar2(100);
5 begin
6 dt1:=to_char(trunc(dt,'hh'),'yyyy-mm-dd hh24:mi:ss');
7 return dt1;
8* end;
SQL> /函数已创建。SQL> edi
已写入 file afiedt.buf 1 declare
2 v date;
3 a varchar2(100);
4 begin
5 v:=to_date('2008-12-21 12:20:20','yyyy-mm-dd hh24:mi:ss');
6 a:=f_date(v);
7 dbms_output.put_line(a);
8* end;
SQL> /PL/SQL 过程已成功完成。SQL> set serveroutput on
SQL> /
2008-12-21 12:00:00
我看你的IDE是date型数据。所以你可以直接这样,就ok了。
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';会话已更改。
SQL> select sysdate from dual;SYSDATE
-------------------
2010-05-13 19:50:19SQL> select trunc(sysdate,'HH24')from dual;TRUNC(SYSDATE,'HH24
-------------------
2010-05-13 19:00:00
CREATE OR REPLACE FUNCTION getdatehour(IDE date)
RETURN DATE
AS
v_ode DATE;
BEGIN
v_ode := to_date(to_char(IDE,'YYYY-MM-DD')||' 12:00:00','YYYY-MM-DD HH24:MI:SS');
RETURN v_ode;
END;
/scott@SZTYORA> select to_char(getdatehour(hiredate),'YYYY-MM-DD HH24:MI:SS') as hiredate
2 from emp;HIREDATE
--------------------------------------
1987-05-23 12:00:00
1981-02-22 12:00:00
1981-04-02 12:00:00
1981-02-20 12:00:00
1981-11-17 12:00:00
1981-12-03 12:00:00
1981-05-01 12:00:00
1981-09-08 12:00:00
1980-12-17 12:00:00
1981-12-03 12:00:00
1982-01-23 12:00:00
1981-06-09 12:00:00
1987-04-19 12:00:00
1981-09-28 12:00:00
RETURN date as OED date;
BEGIN
select trunc(to_date('2008-12-21 12:20:20','YYYY-MM-DD HH24:Mi:SS'),'HH') into ODE from dual;
return ODE;
END;