GRC 5.3 Open SQL select syntax question

[former_member788628]

I am in opensql witin GRC and I am trying to select all the rows from this table but I want t restrict my where clause on the attribute ation date > 2/26/2010 but I need the syntax to convert the actiondate ( 2009-10-19 15:16:56.403 ) so that the where clause understands that I want rows with an actiondate > 2/25/2010.

select * from VIRSA_AE_RQ_HST

where pathname='AVESKA_AUTOAPPROVE'

Attribute (column table names ) for table VIRSA_AE_RQ_HST

REQNO

HST_ACTION

ACTIONDATE

USERID

ACTION_VALUE

DESCRIPTION

PATHNAME

STAGE_NAME

ID

DEPENDENT_ID

Sample Data

1177 ROLE_APPROVED 2009-10-19 15:16:56.403 75000010 YRS:IT:GEN:USER_GEN D11CLNT100 AVESKA_AUTOAPPROVE AUTO_APPROVE 29179 29177

Desired SQL

SELECT * FROM VIRSA_AE_RQ_HST

WHERE

pathname='AVESKA_AUTOAPPROVE'

AND

actiondate > '2/25/2010'

Edited by: Rene Griffith on Feb 26, 2010 9:19 PM

[Former Member]

Hi,

Did you try actiondate > to_date('2/25/2010','mm/dd/yyyy')

Regards

Sudhir

[former_member788628]

Hi

I tried executing this statement but getting this message in opensql

ERROR: The SQL statement "SELECT * FROM VIRSA_AE_RQ_HST WHERE pathname='AVESKA_AUTOAPPROVE' AND actiondate > to_date('2/25/2010','mm/dd/yyyy') " contains the syntax error[s]: - 5:21 - SQL syntax error: the token "(" was not expected here

[Former Member]

Hi Rene, In SQL server below query works fine, please try this

SELECT * FROM SAPEP7DB.VIRSA_AE_RQ_HST WHERE

PATHNAME='AVESKA_AUTOAPPROVE' and

ACTIONDATE > convert(char,'2/25/2010',101)

Regards

[former_member788628]

Thanks but it is complaining about this too. This one is definitley puzzling with the date. Thanks for your persistsence to help.

ERROR: The SQL statement "SELECT * FROM SAPEP7DB.VIRSA_AE_RQ_HST WHERE PATHNAME='AVESKA_AUTOAPPROVE' and ACTIONDATE > convert(char,'2/25/2010',101) " contains the syntax error[s]: - 3:14 - Intermediate-SQL syntax error: CONVERT or TRANSLATE function not allowed - 3:22 - SQL syntax error: "CHAR" is a reserved keyword and cannot be used as an unquoted identifier