×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to change date format for the condition which involves a calculation ?

How to change date format for the condition which involves a calculation ?

How to change date format for the condition which involves a calculation ?

(OP)
Dear Experts,


I want to set the condition where dte_received is between today date and the date 12 months ago.So far I tried the following statement:
WHERE dte_received between (sysdate,-12) and sysdate; (at which System generated an error message)

I run the following:
SELECT SYSDATE FROM dual (which looks like 5/7/2018 10:49:07 AM)
&
SELECT dte_received FROM MYTABLE (looks like 20171213)


How can I change dte_received format in order my statement above would work?

Thank you in advance!

Estersita

RE: How to change date format for the condition which involves a calculation ?

suggest you check the Oracle TO_DATE function.

https://docs.oracle.com/cd/B19306_01/server.102/b1...

You will need to reformat the SYSDATE, your dte_received, or both in order to compare "apples to apples".

As of now, you are comparing an Oracle "date" data type to the data type of dte_received, which is either a number or a character string, but NOT an Oracle "date".

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: How to change date format for the condition which involves a calculation ?

Try

CODE

WHERE dte_received BETWEEN add_months(sysdate,-12) AND sysdate 
This assumes that dte_received is a DATE datatype.

RE: How to change date format for the condition which involves a calculation ?

(OP)
carp,

I tried to do it but get an error ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Actually for SYSDATE it is for sure ( 5/7/2018 10:49:07 AM) . As for dte_received it looks like a string 20171213.
I tried to convert but failed. This is my problem as I am new in Oracle.

Could you please give me a hand?

Thank you for you help!

Estersita

RE: How to change date format for the condition which involves a calculation ?

WHERE to_date(dte_received,'YYYYMMDD') BETWEEN add_months(TRUNC(sysdate),-12) AND TRUNC(sysdate)

The TRUNC removes the time element and the older date must be first in the between clause

Bill
Lead Application Developer
New York State, USA

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close