在WHERE子句中格式化[时间戳]字段值？

Question

我正在编写一个连接两个表的续集ViewPoint查询。第一个表有以下字段：Action、SSAN、MEMNO、RSCODE、USERID和TIMESTAMP，它们的格式为(Ex )。2005-03-11-09.54.18.296000)。其他表只用于通过将两个表连接到NAME上来获得成员SSAN。

当我的查询开始时，用户会看到我指定为StartDate的日期的对话框。这样做的目的是返回所有想要的字段，其中table1.TIMESTAMP值大于用户选择的StartDate。

我当前的查询如下：

 SELECT         memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
 FROM           library1/table1, library1/table2
 JOIN               SSAN.1=SSAN.2
 WHERE          TIMESTAMP>StartDate AND RSCODE='STP'
 UNION 
 SELECT         memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
 FROM           library2/table1, library2/table1
 JOIN               SSAN.1=SSAN.2
 WHERE          TIMESTAMP>StartDate AND RSCODE='STP'
 ORDER BY   sys ASC, memno ASC

其结果是：

Fields TIMESTAMP and STARTDATE in WHERE clause are not compatible.
Cause. . . . .: You are trying to compare two fields that not compatible. One of the following is true:
1 -- One field is numeric and the other is not numeric (character or date/time)
2 -- One field is character and the other is not character (numeric or date/time)
3 -- One field is double-byte and the other is single byte.

这显然是由于我的table1.TIMESTAMP文件在(快递)。2005-03-11-09.54.18.296000)格式和我的查询值StartDate在(例如。01/01/14)。

如何在TIMESTAMP子句中格式化WHERE值，以便将其与StartDate值进行比较？)或者有更好的方法来解决这个问题吗？当涉及到SQL和数据库QWerying时，我仍然很绿色)

编辑：

使用Roopesh的timestamp > cast(StartDate as datetime)建议，结果如下：

 SELECT         memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
 FROM           library1/table1, library1/table2
 JOIN               SSAN.1=SSAN.2
 WHERE          TIMESTAMP>Cast(StartDate as datetime) AND RSCODE='STP'
 UNION 
 SELECT         memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
 FROM           library2/table1, library2/table1
 JOIN               SSAN.1=SSAN.2
 WHERE          TIMESTAMP>Cast(StartDate as datetime) AND RSCODE='STP'
 ORDER BY   sys ASC, memno ASC

There is an error in the WHERE clause. Parser expected ")". Continue Anyway?是选定的

我选择日期01/01/14，它在sql中显示为"01/01/14 NAME(StartDate)，并接收：

 Identifier 'AS' preceding ' datetime)' is used incorrectly.
    Proper SQL syntax rules have been viloated. The identifier cannot occur where it has been found in the statement. Instad of 'AS', SQL syntax rules allow only: ) ,. If you are using *SEQUEL object authority checking, you cannot use runtime variables in place of the allowed values.

使用Notulysses的建议：

变量StartDate为Date类型，长度为10，默认为01/01/2014。

 SELECT         memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
 FROM           library1/table1, library1/table2
 JOIN               SSAN.1=SSAN.2
 WHERE          CAST(TIMESTAMP as Date)>StartDate AND RSCODE='STP
 UNION 
 SELECT         memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
 FROM           library2/table1, library2/table1
 JOIN               SSAN.1=SSAN.2
 WHERE          CAST(TIMESTAMP as Date)>StartDate AND RSCODE='STP
 ORDER BY   sys ASC, memno ASC

There is an error in the WHERE clause. Parser expected ")". Continue Anyway?是选定的

我选择日期01/01/14，它在sql中显示为"01/01/14 NAME(StartDate)，并接收：

Identifier 'AS' preceding ' Date)>Sta' is used incorrectly.
Proper SQL syntax rules have been viloated. The identifier cannot occur where it has been found in the statement. Instad of 'AS', SQL syntax rules allow only: ) ,. If you are using *SEQUEL object authority checking, you cannot use runtime variables in place of the allowed values.

与常规ViewPoint相比，SQL语法可能很奇怪。很高兴我们不久将把它作为我们店里的一种标准工具.

EDIT2 (解决方案)：

正如JamesA所指出的，诀窍是使用日期函数：DATE(TIMESTAMP)。

Answer 1

使用日期函数将时间戳转换为日期部分：

SELECT         memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM           library1/table1, library1/table2
JOIN               SSAN.1=SSAN.2
WHERE          DATE(TIMESTAMP)>StartDate AND RSCODE='STP'
UNION 
SELECT         memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM           library2/table1, library2/table1
JOIN               SSAN.1=SSAN.2
WHERE          DATE(TIMESTAMP)>StartDate AND RSCODE='STP'
ORDER BY   sys ASC, memno ASC

Answer 2

我不是续集专家..。

但是我怀疑有一种方法可以告诉它你想要提示一个真正的约会。

然后，查询可以使用TIMESTAMP_ISO(StartDate)将日期转换为时间戳。

注意，将日期转换为时间戳比将时间戳转换为日期要好，因为表列中的值转换将导致索引无法使用。