I have this DATETIME field in my oracle database shown in the image
I'm trying to make a query which returns something between specific dates but this query returns nothing.
SELECT *
FROM tbl_meter
WHERE TO_DATE(DATETIME,'DD/MM/YYYY') BETWEEN '%s' AND '%s'
What am I missing?
EDIT:
SELECT * FROM tbl_meter WHERE DATETIME BETWEEN '15/01/2014' AND '07/01/2014'
SELECT *
FROM tbl_meter
WHERE TRUNC(DATETIME) BETWEEN to_date('%s','DD/MM/YYYY') AND to_date('%s','DD/MM/YYYY');
Asssuming DATETIME is of DATE data type.
If it is of TIMESTAMP datatype then,
SELECT *
FROM tbl_meter
WHERE TRUNC(CAST(DATETIME AS DATE)) BETWEEN to_date('%s','DD/MM/YYYY') AND to_date('%s','DD/MM/YYYY');
If it is VARCHAR, then
SELECT *
FROM tbl_meter
WHERE TRUNC(To_DATE(DATETIME,'Dd/MM/YYYY HH24:MI:SS.FF6')) BETWEEN to_date('%s','DD/MM/YYYY') AND to_date('%s','DD/MM/YYYY');
cast in the second version, but it doesn't hurt and I guess spells out what's happening. (The value returned is always of data type DATE, even if you specify a different datetime data type for date).It looks like you are using the same parameter twice. I expect you to have
select *
from tbl_meter
where to_date(datetime, 'DD/MM/YYYY') between to_date('%s1', 'DD/MM/YYYY') and to_date('%s2', 'DD/MM/YYYY')
Try this way
SELECT *
FROM tbl_meter
WHERE TO_DATE(DATETIME,'DD/MM/YYYY') >='%s' AND TO_DATE(DATETIME,'DD/MM/YYYY') <'%s'
datetimecolumn? If it is aDATEcolumn, thelikeoperator does not make any sense (plus applyingto_date()on adatecolumn to convert it to adatedoesn't make sense)to_date()on atimestampcolumn is totally useless.to_date()converts a string into a date. When you apply it on atimestampcolumn, the timestamp first gets converted into a string (subject to implicit data type conversion based on various NLS settings) which then gets converted back to adatevalue. Simply usewhere datetime between %1 and %2and you should be fine. You maybe want to apply thetrunc()function on the value to remove the time part.