CODEWITHSUNDEEP
sqloracle-databasedate-comparison
drup
drup

Reputation: 1157

Search between dates in SQl

I have a date-picker in my form which generate one start date and end date while submitting a form. The date format is '#date_format' => 'Y-m-d', for both. From my oracle DB, i need to compare theses two inputs against column value TIMESTAMP with date format date('Y-m-d'). My SQL query is 

SELECT TITLE 
FROM TABLE 
WHERE TIMESTAMP BETWEEN '2015-07-15' AND '2015-07-30' 
GROUP BY TITLE ORDER BY TITLE ASC

Can i search like this?I didn't got any results with this query.

Upvotes: 2

Views: 100

Answers (2)

Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

Try this for MSSQL Query

SELECT TITLE FROM TABLE WHERE  convert(varchar(50),TIMESTAMP ,111)
BETWEEN '2015-07-15' AND '2015-07-30' GROUP BY TITLE ORDER BY TITLE ASC

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

WHERE TIMESTAMP BETWEEN '2015-07-15' AND '2015-07-30'

You are comparing a DATE with string. You must use TO_DATE to explicitly convert a string into date.

For example,

WHERE TIMESTAMP 
BETWEEN 
   TO_DATE('2015-07-15', 'YYYY-MM-DD') 
AND 
   TO_DATE('2015-07-30', 'YYYY-MM-DD')

When you only have the date element with fixed literal format, I prefer ANSI date literal

DATE '2015-07-15'

The above query could be written with less code using ANSI date literal:

WHERE TIMESTAMP 
BETWEEN 
   DATE '2015-07-15'
AND 
   DATE '2015-07-30'

Upvotes: 2

Related Questions