db2查询时间的sql语句(db2查询数据库当前时间)

admin sql技巧 2022-12-06 2 0

本文目录一览:

关于DB2的日期计算的SQL语句,大神速进

SELECT DATE(LEFT('201202', 4) || '-'||RIGHT('201202', 2)||'-1') - 1 MONTH,

DATE(LEFT('201202', 4) || '-'||RIGHT('201202', 2)||'-1') + 1 MONTH

from sysibm/sysdummy1

DATE(LEFT('201202', 4) || '-'||RIGHT('201202', 2)||'-1') - 1 MONTH这样拿到的是日期类型,需要cast as char,然后截取就可以了

db2处理日期很垃圾的

db2要查询两个日期之间的记录sql语句如何写

请参考如下SQL,具体逻辑是:差值的绝对值小到大排序,取第一行即可。

如果需要其他列的值,把取值放where中…… 这样就算有重复值(差值的绝对值相同)也能揪出来~

select T from table(select T, abs($T_STR-T) as diff from A) order by diff fetch first 1 rows only

例子:

db2 = select * from a

T

--------------------------

2014-04-02-01.00.00.000000

2014-05-02-01.00.00.000000

2014-06-02-01.00.00.000000

3 record(s) selected.

db2 = values timestamp('2014-05-03-01.00.00.00000')

1

--------------------------

2014-05-03-01.00.00.000000

1 record(s) selected.

db2 = select T from table(select T, abs('2014-05-03-01.00.00.00000'-T) as diff from A) order by diff fetch first 1 rows only

T

--------------------------

2014-05-02-01.00.00.000000

1 record(s) selected.

db2 =

DB2请教取任何两个时间段内的所有年月的SQL语句如何实现 比如 20050903 -- 20060201,得到 200509 200510

其实很多需求没有必要老想着用一个语句搞掂,可以试试用存储过程或者函数完成。假如你真的不想用存储过程或者函数,你可以参照下面的语句实现你的要求:

WITH TEMP1(COL1,COL2) AS

(

VALUES (DATE('2005-09-03'), DATE('2005-09-03') + 1 MONTHS)

UNION ALL

SELECT COL1 + 1 MONTHS, COL2 + 1 MONTHS

FROM TEMP1

WHERE COL1 + 1 MONTHS = DATE('2006-02-01')

)

SELECT

YEAR(COL1) * 100 + MONTH(COL1) AS MONBEGIN,

YEAR(COL2) * 100 + MONTH(COL2) AS MONEND

FROM TEMP1

你只要将语句里面的日期替换一下就能实现你的要求。 语句执行后的结果是这样:

MONBEGIN MONEND

200509 200510

200510 200511

200511 200512

200512 200601

200601 200602

评论