March 17, 2010 at 8:55 am
SELECT * FROM FRATERNAL.CONTACTS WHERE MODIFIED_DT = '2009-10-06-12.22.15.008000'
returns 1 row
SELECT * FROM FRATERNAL.CONTACTS WHERE MODIFIED_DT LIKE '2009-10-06 %'
returns an error: No authorized routine named "LIKE" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
What is wrong with my syntax?
March 17, 2010 at 9:13 am
Jpotucek (3/17/2010)
SELECT * FROM FRATERNAL.CONTACTS WHERE MODIFIED_DT = '2009-10-06-12.22.15.008000'returns 1 row
SELECT * FROM FRATERNAL.CONTACTS WHERE MODIFIED_DT LIKE '2009-10-06 %'returns an error: No authorized routine named "LIKE" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
What is wrong with my syntax?
Your trying to us a wildcard where you have a date. Try this:
select
*
from
FRATERNAL.CONTACTS
where
MODIFIED_DT >= '2009-10-06' and
MODIFIED_DT < '2009-10-07'
March 17, 2010 at 9:19 am
EDIT: Lynn, now you were quicker than me!
The problem is that the column is a DATETIME and you can't use LIKE with DATETIME values.
Use BETWEEN or a combination of < and > instead:
SELECT *
FROM FRATERNAL.CONTACTS
WHERE MODIFIED_DT BETWEEN '2009-10-06' AND '2009-10-06 23:59:59'
--or
SELECT *
FROM FRATERNAL.CONTACTS
WHERE MODIFIED_DT >= '2009-10-06' AND MODIFIED_DT < '2009-10-07'
/Markus
March 17, 2010 at 10:00 am
Hunterwood (3/17/2010)
EDIT: Lynn, now you were quicker than me!The problem is that the column is a DATETIME and you can't use LIKE with DATETIME values.
Use BETWEEN or a combination of < and > instead:
SELECT *FROM FRATERNAL.CONTACTS
WHERE MODIFIED_DT BETWEEN '2009-10-06' AND '2009-10-06 23:59:59'
--or
SELECT *
FROM FRATERNAL.CONTACTS
WHERE MODIFIED_DT >= '2009-10-06' AND MODIFIED_DT < '2009-10-07'
/Markus
I would not use the following as you will missing records > 2009-10-06 23:59:59 and < 2009-10-07 00:00:00. When working with dates, really should use >= and < checks.
SELECT *
FROM FRATERNAL.CONTACTS
WHERE MODIFIED_DT BETWEEN '2009-10-06' AND '2009-10-06 23:59:59'
March 17, 2010 at 11:04 am
Thank you all!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply