ORACLE determines whether character type can be converted to date type ORA-01847 ORA-01843 (Turn)

  There is a user's table to be imported into our system, the original table TM_WWM date is varchar (8) Type, the format is YYYYMMDD. Before importing to my system, we need to see if the date is legal SQL1 Select To_date (saledate, ' YYYYMMDD ') from TM_WWM b                                        * ERROR at line 1:ora-01847:day of month must Being between 1 and last day of month        from the tip you can see that character type dates have illegal characters, such as 00 days, or February 30. Start by making a date table that stores the start date for decades without months, and then compare the date fields in the two tables to see if it's legal. Again or not using the middle table, used as a child to determine the size of the month of the method, 1,3,5,7,8,10, wax, this 7 months is 31 days, directly write SQL comparison, February alone to judge. But these two methods are more rigid, so think of their own and their own comparison of methods, as follows. SQL2 Select COUNT (*) from TM_WWM   where saledate are not null and substr (saledate,1,4) between 1970 and 2008 and Subs TR (saledate,5,2) between ' and ' and Saledate  between to_char (to_date (SUBSTR), ' saledate,1,6 '), ' YYYYMMDD ') and To_char (Add_months (To_date (SUBSTR (saledate,1,6), ' Yyyymm '), 1), ' YYYYMMDD ') and To_char (ADD_months (To_date (SUBSTR (saledate,1,6), ' Yyyymm '), 1), ' YYYYMMDD ')                                  * ERROR at line 7:ora-01843:not A valid month      can see To_char to_date (Subs TR (saledate,1,6), ' yyyymm '), ' YYYYMMDD ') is the first day of the month on which the date is obtained, for example, the saledate is 20070312, then this is 20070301 (To_char) add_months (to_ Date (SUBSTR (saledate,1,6), ' Yyyymm '), 1, ' YYYYMMDD ') is the first day of the next month of this date, is 20070401, then between these two dates should be the legal date.        but it reported a monthly error, so use the following SQL to determine whether the month is the problem SQL3 select DISTINCT (To_char add_months (to_date (SUBSTR (saledate,1,6), ' Yyyymm '), 1), ' YYYYMMDD ')   from TM_WWM   where saledate are not null and SUBSTR (Saledate, 1,4) between 1970 and 2008 and SUBSTR (saledate,5,2) between ' and '        This SQL is OK, stating All the years between 1970 and 2008 are fine, and can be formatted as a date, which can only mean that day has a problem. SQL4 Select COUNT (*) from (SELECT * from TM_WWM  where saledate is not null and substr (saledate,1,4) between 1970 and 2008 and SUBSTR (saledate,5,2) between ' ' where ') where Saledate  between to_char (to_date (SUBSTR (saledate,1,6), ' yyyymm '), ' YYYYMMDD ') and To_char (add_months (To_date (SUBSTR (saledate,1,6), ' Yyyymm '), 1), ' YYYYMMDD ') 1981098 lines note the difference between SQL2 and SQL4, SQL2 is the same level as the limit So SQL is performed from right to left (previously thought not all of the rbo are from right to left, so always do not care, today to suffer, you can also turn the order upside down, the effect and SQL4 the same, SQL4 the inside, in accordance with the circumstances of the year "date" Between 1th this month and next month, 1th. Here you can say that the character dates for select all match the date format. SQL4 Select count (to_date (saledate, ' YYYYMMDD ') from (SELECT * from TM_WWM   where saledate are not nul L and substr (saledate,1,4) between 1970 and 2008 and SUBSTR (saledate,5,2) between ' and ' ') where Saledate  BETW Een to_char (to_date (SUBSTR (saledate,1,6), ' yyyymm '), ' YYYYMMDD ') and To_char (Add_months (to_date ), ' Yyyymm '), 1, ' YYYYMMDD ') but not the ERROR at line 1:ora-01839:date not valid for month specified sp;  See To change the SQL4 count (*) to count (tO_date (saledate, ' YYYYMMDD '), i.e. To_char (To_date (SUBSTR), ' saledate,1,6 '), ' yyyymm ') can be executed, but YYYYMMDD ( Saledate, ' YYYYMMDD ') cannot be executed. Careful observation of the two statements is the difference between to_date (SUBSTR (saledate,1,6), ' yyyymm ') and To_date (saledate, ' YYYYMMDD '), Or is it a question of the day rather than the month (I don't know why Oracle is a month problem in general). In other words, if the saledate is 20060230, then between To_char (To_date SUBSTR (saledate,1,6), ' yyyymm '), ' YYYYMMDD ') and To_char (add_ The range of MONTHS (To_date (SUBSTR (saledate,1,6), ' Yyyymm '), 1), ' YYYYMMDD ') is 20060201 and 20060301, while character 20060230 is also within this range, So the conditions can be chosen, while To_date (Saledate, ' YYYYMMDD ') is illegal. The 20060201 and 20060301 ranges were considered at that time because the time and minutes were taken into account, and the case was not seconds, so the scope was changed to SQL5 SELECT * FROM (SELECT * TM_WWM   where saledate i s not null and substr (saledate,1,4) between 1970 and 2008 and SUBSTR (saledate,5,2) between ' and ' 12 ') where Saledate between To_char (To_date (SUBSTR (saledate,1,6), ' yyyymm '), ' YYYYMMDD ') and To_char (Add_months (to_date SUBSTR (saledate,1,6), ' Yyyymm '), 1) -1 , ' YYYYMMDD ')This time range is 20060201 and 20060228, running normally.       This example determines whether the character date is a valid date format using its own comparison with the way a SQL implementation, more flexible, portability is also very high. Reprinted from Http://sunmoonking.spaces.live.com/blog/cns!E3BD9CBED01777CA!879.entry