CODEWITHSUNDEEP
sqloracle-database
rayban012
rayban012

Reputation: 45

Compare Two Dates as Integers, Oracle SQL

I'm looking for a way to compare two dates in a SQL query, where dates are stored as integers in separate columns, as year, month, and day. (Please note that this wasn't my choice!) If the date was October 12th, 2011, for example, you'd have values of 2011, 11, and 12, respectively.

My original stab at it was to do it arithmatically; this however sort of fell through when I realised that I will not know which of the two dates will be greater. I've tried to look into date support in SQL, but unfortunately I haven't really found anything that helps me out. I've found a few ways to convert to date formats in other flavours, such as MySQL, but nothing for Oracle's implementation.

Upvotes: 2

Views: 1786

Answers (2)

ivanatpr
ivanatpr

Reputation: 1880

Would this work in the case where the year is not necessarily four digits long?

If you have two character years ("98" for 1998, "02" for 2002) then you can use the RR year format, which will intelligently convert those. It will also correctly handle four digit years if both types happen to be intermingled.

examples:

select to_date(to_char(02)||'-'||to_char(12)||'-'||to_char(30),'RR-MM-DD') from dual;

select to_date(to_char(1998)||'-'||to_char(12)||'-'||to_char(30),'RR-MM-DD') from dual;

Upvotes: 0

steve godfrey
steve godfrey

Reputation: 1234

Assuming your columns are called something like day, mon and year in table xyz

select to_date(to_char(day)||to_char(mon)||to_char(year),'ddmmyyyy')
from xyz

will convert the numbers to a date. You can then manipulate them as you wish.

Added in response to comment :

Probably the easiest way to exclude combinations of dd mon and yyyy that wouldn't convert to dates, would be to do the to_date in pl/sql, then have an invalid_date exception handler to trap problem combinations. If you have to use sql, I suppose you could try and exclude the invalid combinations before doing the to_date, so something like

SELECT to_date(to_char(day)||to_char(mon)||to_char(year),'ddmmyyyy')
FROM   xyz
WHERE  to_char(day)||to_char(mon) NOT IN ('3002','3102','3104','3106','3109','3111')
AND    (to_char(day)||to_char(mon) != '2902'
    OR to_char(year) IN (2008,2004,1996,1992,1988,...))

The ... would need to hold as many leap years as your data.

Upvotes: 3

Related Questions