Validating date in oracle
Asked: July 01, 2001 - pm UTC Answered by: Tom Kyte - Last updated: November 19, 2010 - am UTC Category: SQL*Plus - Version: 8 and above Viewed 50K times!
This question is Hi Tom, I've been using Oracle for Sometime now, I use a lot of MSSQL and Sybase, I'm trying to load some ascii file using SQL Loader(sqlldr), I want to know if there is any oracle function equvivalent to ISDATE() function in MSSQL or Sybase.
NEXT_DAY returns the date of the first weekday named by char that is later than date.
The return type is always DATE, regardless of the datatype of date.
SELECT TO_CHAR ( NEXT_DAY ( LAST_DAY ( ADD_MONTHS (TRUNC(SYSDATE,'Y'), ROWNUM-1))-7, TO_CHAR (TO_DATE('', 'DD-MM-YYYY'),'DAY') ), 'DD. YYYY') "Last Saturdays in 2004" FROM ALL_OBJECTS WHERE ROWNUM The "" is just a random date that we knew was a Saturday—any Saturday would do.
This is done instead of using "SAT" in the query for international reasons, because in languages other than English, "SAT" isn't Saturday.
This means no more worries about how many seconds in a day and all those cumbersome calculations.The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. For example, the ADD_MONTHS function lets you add or subtract months from a date. YYYY: HH24: MI: SS') "Today", trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec", trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min", trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs", trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days" FROM emp; Check out the above query for a possible solution on how to extract the individual time intervals for a subtraction of two dates.The MONTHS_BETWEEN function returns the number of months between two dates. YYYY: HH24: MI: SS') "Hiredate", TO_CHAR(&Today,'DD. The fractions could be reduced but we wanted to show all the numbers to emphasize the calculation.We could use this isdate function, but was just checking if there is any other way. I put date in quotes because the field is a string of characters that are sometimes "999999" or "000000".Thanks, Aji November 19, 2010 - am UTC since you have just yyyymmdd - you could easily optimize this. To make matters worse, when the data actually is a date, it is not in format that can be sorted as is (ie "rrmmdd") but instead is "mmddrr", and since it has 2 digit years, sorting on sub-strings will put 1999 (99) after 2001 (01).
Is there a way to stop people filling in date fields manually?