02-27-2010 3:18 AM
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
03-02-2010 12:03 PM
03-02-2010 12:03 PM
03-12-2010 2:38 PM
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
03-12-2010 3:41 PM
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
03-12-2010 3:57 PM
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