CODEWITHSUNDEEP
sqloracle-databasedatetimeplsql
Cooluser
Cooluser

Reputation: 11

Where condition between dates in pl/sql

WHERE Customer.LastTranDate BETWEEN 
udf_functions.udf_oracledatetimetotdatetime(to_date('2012/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')) 
AND    
udf_functions.udf_oracledatetimetotdatetime(to_date('2016/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS'))

The above condition worked perfect for specific dates but I want to check the same condition for a period of 12 months i.e between today's date and date 12 months ago.

I tried the following but it didn't work. 

WHERE Customer.LastTranDate BETWEEN 
udf_functions.udf_oracledatetimetotdatetime(to_date(to_char(sysdate,'YYYY/MM/DD HH24:MI:SS')))
AND 
udf_functions.udf_oracledatetimetotdatetime(to_date(to_char(add_months(sysdate,-12),'YYYY/MM/DD HH24:MI:SS')))

Can anyone help?

Thanks!!

Upvotes: 0

Views: 317

Answers (2)

BeRightBack
BeRightBack

Reputation: 228

No need for date conversion on your WHERE part of query.

If you need date in specific format, you do that on SELECT part, and leave WHERE as it is

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Huh? Why all the conversion to and back from strings?

WHERE Customer.LastTranDate BETWEEN
          udf_functions.udf_oracledatetimetotdatetime(add_months(sysdate, -12)) AND
          udf_functions.udf_oracledatetimetotdatetime(sysdate

If the function takes an Oracle date as an argument (as suggested by the to_date(), then there is no need to convert to strings and back to dates.

I don't really understand why you have a udf to do such conversions. That can only hurt performance.

Upvotes: 1

Related Questions