CODEWITHSUNDEEP
sqloracle-databasedate
Brett Rinehold
Brett Rinehold

Reputation: 11

Rolling 12 months oracle

I am writing a query in Oracle that I want to automate. I currently have a where clause that has the following:

TRAN.DATE BETWEEN 20160101 AND 20161231

I want the query to be a 12 month look back from the current date. I think I can do it if the date was in a date format using the following:

TRAN.DATE BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE

But the date format in the table is in an integer format. I have tried to the TO_DATE function but I can't get the format right.

Upvotes: 0

Views: 7301

Answers (3)

zarruq
zarruq

Reputation: 2465

According to Oracle documentation,

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype

so you need to first cast integer column to char or any of the accepted datatype specified above, and then cast it to DATE as below.

SELECT TO_DATE(to_char(20160101),'YYYYMMDD') as DATE1 FROM DUAL

Result

DATE1
-------------------
01.01.2016 00:00:00

so you need to rewrite your query as below.

TO_DATE(to_char(TRAN.DATE),'YYYYMMDD') BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE

Also if you need only date part, you amy also need to trunc sysdate.

You can check the demo here

Hope this will help.

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74660

Yikes, you mean your database table literally has an integer of 20170104 (twenty million, one hundred seventy thousand, one hundred and four) and this represents the forth of Jan, this year?

Setting aside any concerns I have about this not being the best way to store a date, it's probably going to be easiest to write and also understand, if you convert your date to char and then to int:

WHERE DATECOLUMN BETWEEN
  TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYYMMDD')) AND 
  TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD'))

There's no point getting clever and trying to deal with it as an integer math problem by subtracting 1200 or 10000 etc as a number because it'll make your query hard to understand and have no performance gain at all over a couple of number conversions as per this way

You'd be better off changing those dates to actual dates right now, before the codebase grows any bigger and someone decides it's a good idea to add more date columns stored as ints

Upvotes: 2

Trung Duong
Trung Duong

Reputation: 3475

You could try 

TO_DATE(TRAN.DATE, 'YYYYMMDD')  BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE

Upvotes: 0

Related Questions