CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloper
michael
michael

Reputation: 681

Oracle Sql - Add days to variable of type timestamp

I have a variable 'mydate' of type 'timestamp' which I want to compare it against the 'record_date' (which is a database column)

My query is to get the records where mydate=record_date, but my query (record_date=:mydate) only gives the result where record_date is like '06/10/13 12:00:00.000000000 AM'. If the record_date has some different hh:mm:ss values like '06/10/13 02:23:32.000000000 AM', the equality operators does not give any output.

record_date>=:mydate works, but it also gives output for the dates greater than 6, i.e. 7 and so on.

How to write a comparison like this record_date>=:mydate and record_date<=:mydate+1 (mydate+1) is having problem. Please suggest

Upvotes: 1

Views: 661

Answers (2)

riverdog
riverdog

Reputation: 54

You are mostly correct...the question above though, looks like you are asking about a date range...since there is objiously more than one way to write the query, so you can either write where a = b or you can write where a > b-1 and a < b+2. If you are only considering a = b, then yes, to_char = to_char will work, as will trunc(a) = trunc(b).

However, if you need to see if a date is greater than another date, you will just want to make a small edit as currently it would look at dd first, then mm, and last year...that would not give you correct results, so you can just change the date order to yyyy-mm-dd. :)

Upvotes: 0

michael
michael

Reputation: 681

The above problem can be solved by using the below query

TO_CHAR(RECORD_DATE, ''DD-MON-YYYY'') = TO_CHAR(:MYDATE, ''DD-MON-YYYY'')

Upvotes: 1

Related Questions