Topic: 比较两个时间大小的sql语句

  Print this page
1.比较两个时间大小的sql语句 Copy to clipboard
Posted by: milo
Posted on: 2003-12-18 15:44

SELECT * FROM tbl
WHERE to_date(date1,'DD-MM-YYYY HH:MI:SS')>to_date(date2,'DD-MM-YYYY HH:MI:SS')
AND other_condition;
看有没有结果返回，没有的话就date1不大于date2。
2.Re:比较两个时间大小的sql语句 [Re: milo] Copy to clipboard
Posted by: dingligang
Posted on: 2003-12-18 15:51

请问 to_date 是标准SQL支持的函数吗？就像 SUM()。
3.Re:比较两个时间大小的sql语句 [Re: milo] Copy to clipboard
Posted by: ditty
Posted on: 2003-12-18 16:46

No,it's suported only by Oracle.
4.Re:比较两个时间大小的sql语句 [Re: milo] Copy to clipboard
Posted by: ditty
Posted on: 2003-12-18 16:50

milo wrote:
SELECT * FROM tbl
WHERE to_date(date1,'DD-MM-YYYY HH:MI:SS')>to_date(date2,'DD-MM-YYYY HH:MI:SS')
AND other_condition;
看有没有结果返回，没有的话就date1不大于date2。

这是比较两个时间列式否存在大于对方的纪录而已，没啥意义！
即使要比，也不要用*，而是用count(*)比较好。判断返回值是否大于0就好了！
5.Re:比较两个时间大小的sql语句 [Re: milo] Copy to clipboard
Posted by: milo
Posted on: 2003-12-19 09:44

to_date 是标准SQL支持的函数,
这个标准是 ANSI SQL-92
6.Re:比较两个时间大小的sql语句 [Re: milo] Copy to clipboard
Posted by: milo
Posted on: 2003-12-19 09:49

You can do that like this :

select to_date(DATA1,'YYYY-MM-DD HH:MI:SS')-to_date(DATA2,
'YYYY-MM-DD HH:MI:S')
as TEST
from dual;

判断返回的数据，它的单位是天，
判断它的符号就知道他们的大小了
7.Re:比较两个时间大小的sql语句 [Re: milo] Copy to clipboard
Posted by: why
Posted on: 2003-12-19 14:53

> to_date 是标准SQL支持的函数,
> 这个标准是 ANSI SQL-92
Please prove it!
To my understanding,TO_DATE is Oracle-specific.
The ANSI standard function is CAST()
<cast specification> ::= CAST <left paren> <cast operand> AS <cast target> <right paren>
i.e. CAST(value AS datatype)
(from http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt )

And "from dual" is Oracle for sure Smile

For a list of ANSI SQL Reserved Words, check http://developer.mimer.se/validator/sql-reserved-words.tml

   Powered by Jute Powerful Forum® Version Jute 1.5.6 Ent
Copyright © 2002-2021 Cjsdn Team. All Righits Reserved. 闽ICP备05005120号-1
客服电话 18559299278    客服信箱 714923@qq.com    客服QQ 714923