Hmmm,

I feel I should know how to do this but either I've got brain fade or there is no simple way (which I find hard to believe).

I just want a way of testing a character string to see if it is in fact a valid date, without causing a runtime error it it isn't.

I actually want to use this in a CHECK constraint, but it would be the same problem to select rows where the value in some character column was a valid date.

So, for example, I sort of expect to be able to say something like:

SELECT * FROM table WHERE TO_DATE(char_column, "YYYYMMDD") IS NOT NULL

Now, the test in bold may not be "correct" SQL but I'm sure you can see what my intention is.

It doesn't work because the TO_DATE function causes a runtime error as soon as it encounters a row where char_column is not a valid date in the specified format.

Anyone know how to do this in a straightforward way?

[I'm using Oracle 10g BTW]

Richard,

It seems like you have a great luxury if you can guarantee that the string is indeed YYYYMMDD.

The problem that we typically run into is that we want to allow character input on a date field. (Example: Enter date, "T" for today, "N" for next week.)

Unfortunately, whenever we've done this, we've had to recode all the ways that dates can be entered (presented with a US date entry bias)
MMDDYY
MM/DD/YY
MMDDYYYY
MM/DD/YYYY
M/D/YY
M/D/YYYY
M/DD/YY
M/DD/YYYY
MM/D/YY
MM/D/YYYY

It's a labor of love.

Regards,

Joseph

Hi ,

What you need to do is create a function using pl/sql and include an exception
handler and use it in place of the std to_date function, something like this:

create or replace function GetDate (vDate varchar2, vMask varchar2) returns varchar2 is

vReturnDate date;

begin
begin
vReturnDate = to_date(vDate, vMask);
return(vReturnDate);
end
exception
when value_error then
return 0;
end GetDate;


HTH

Hi,

For ease, why not check if your char_column is not blank (or not null) then do your TO_DATE, but then
this is not full proof since your data in char_column may not have the correct YYYYMMDD format.

SELECT * FROM table WHERE and char_column != ' '

guest23 - Thanks, I can see I could equally create something like..

function Str_Is_Valid_Proiv_Date(vDate varchar2, vMask varchar2) returns boolean

..this could additionally check that a date was within the range ProIV can handle and I imagine I could use it directly in WHERE clauses and CHECK conditions etc.

I was hoping there was a simpler way than having to process exceptions in PL/SQL though! Kind of tough to have to go that far in order to use Oracle's date processing capabilities for such an apparently simple thing!


Pinoy - Thanks, but that's kind of where I started from anyway as it happens

For ease, why not check if your char_column is not blank (or not null) then do your TO_DATE, but then
this is not full proof since your data in char_column may not have the correct YYYYMMDD format.

As a point of technical interest, I thinks that's not actually foolproof for a much more basic reason:
I don't think SQL is defined to have "short-circuit evaluation". In other words, the expression on the right hand side of an AND or OR may still be evaluated even when the 'overall answer' is obvious from evaluating just the expression on the left hand side. Furthermore, it may not even be guaranteed that the expression on the left hand side is evaluated before the expression on the right hand side.

I was hoping there was a simpler way than having to process exceptions in PL/SQL though! Kind of tough to have to go that far in order to use Oracle's date processing capabilities for such an apparently simple thing!

Maybe it's having to go too far but perhaps not. In any environment
if you don't know the contents of a string (say) then performing any
specific conversion may cause run-time error. So, we validate the
string first. Yeah that can be costly in time. Oracle exception
handling is a very easy solution to this problem.

Of course there is the other approach of enforcing the data will
always be correct in the column.... (default value/value set by trigger etc etc)

In any environment if you don't know the contents of a string (say) then performing any specific conversion may cause run-time error.

It may, as in this case, but it doesn't have to. A better "environment" would either return some kind of error indication when a conversion fails (eg. TO_DATE could return NULL) or would provide a separate function to test validity (eg. an IS_DATE function)

In my ideal world, the environment (API) would be flexible, accepting the error return or not is your coding choice and a runtime error occurs only when your code chooses to ignore it

Oracle exception handling is a very easy solution to this problem.

Not sure I agree that having to use a separate language counts as easy
It wouldn't be easy (without assistance) for someone who'd never used PL/SQL or a language with exception handling.
Nor is it as easy as it should be to make the solution portable across databases.

Your help is appreciated and the solution you suggest is perfectly usable and may well be the best solution, I'm merely saying that I'm surprised SQL is so inadequate on its own in this case.

Of course there is the other approach of enforcing the data will always be correct in the column.... (default value/value set by trigger etc etc)

That is what I'm doing - I'm trying to add a CHECK constraint

A better "environment" would either return some kind of error indication when a conversion fails (eg. TO_DATE could return NULL)


or would provide a separate function to test validity (eg. an IS_DATE function)

In my ideal world, the environment (API) would be flexible, accepting the error return or not is your coding choice and a runtime error occurs only when your code chooses to ignore it

Not sure I agree that having to use a separate language counts as easy

Nor is it as easy as it should be to make the solution portable across databases.

merely saying that I'm surprised SQL is so inadequate on its own in this case.

good points. And you make me agree it should be possible in SQL.
I don't know exactly what you're doing but is it possible to simply
convert to use a date type column rather than having dates stored
as alphas as you appear to have?

Not sure that to_Date returning null would work - that's a valid entry in a date column
but IS_DATE might be an idea.

I don't know exactly what you're doing but is it possible to simply convert to use a date type column rather than having dates stored as alphas as you appear to have?

What I'm actually trying to do is use a NOT NULL VARCHAR2 column to contain either a valid date or be "blank" to indicate "no date specified" or similar.

I'm not using a DATE column precisely because I want to avoid using NULL values.
I want to find how to make this work in the most simple and effective way so I could extend the technique to NUMBERs, TIMESTAMPSs, INTERVALs and so on.
The idea is also extensible to recording multiple "data statuses" such as "UNKNOWN", "N/A", "WITHHELD" and so on although I don't need that today.

And, yes, I know purists would say I'm not "supposed" to use RDBMS columns in this way. I'm perfectly aware this is a "compromised" design but there are good reasons for the compromise. Ironically, given our discussion of runtime errors, one reason is to try and ensure that there is a runtime error whenever a programmer does not handle the data properly.

Obviously I need CHECK constraints to control this stuff properly when data could come from ProIV and/or other sources.

Not sure that to_Date returning null would work - that's a valid entry in a date column

Well, I was just throwing that out as clarification - I don't imagine Oracle are going to change it for me!
Nevertheless, TO_DATE presumably does return NULL anyway when it gets NULL argument(s).. [hmm, should I test this before posting.. naah]
Also, stating the painfully obvious, NULL is not a valid entry in a NOT NULL DATE column - which, as mentioned, I want all my columns to be.

Edited by Richard Bassett, 07 January 2005 - 02:27 PM.

G'Day Richard,

And, yes, I know purists would say I'm not "supposed" to use RDBMS columns in this way.

You'll probably tell me to boil my head, but I was taught that this kind of thing was a real no-no when it came to database design. As much as possible, the table definitions should be intuitive, meaning that if one created a NOT NULL column to hold a date, then there really should be a valid date in that column. If a non-valid date was allowable (i.e. " "), then another column should be introduced (in this case a Y/N flag) to indicate that fact on a row by row basis.

Once again, in my opinion the introduction of implied logic into columns is dangerous (i.e. if this column is blank then do something, otherwise it's just a plain old date). This should not only help with the problem you have now, but it should also help developers who may look at this application in the future (and heaven help them if you've already had that meeting with the bus ).

My two cents.

Shaun

You'll probably tell me to boil my head, but I was taught that this kind of thing was a real no-no when it came to database design.

Yeah, well I can't really tell you to boil your head if you're largely right can I.
I can only go.. "/sigh/ Now I have to spend 20 minutes creating an intelligible answer."

It's a no-no of sorts, but, IMHO, it's nothing like as big a no-no as letting application programmers try to process NULLs in ProIV. SQL NULLs are problematic (vast literature), ProIV's attemps to fit them into the ProIV "variable model" just adds insult to injury.
In fact, matters are so bad that, IMHO, avoiding NULLs in a ProIV application is much more important than having a "purist" database design in this respect.

As much as possible, the table definitions should be intuitive, meaning that if one created a NOT NULL column to hold a date, then there really should be a valid date in that column.

Eh? Then it might as well be a DATE type column surely? In my case it then has to allow NULLs because you sure as hell can't put some arbitrary valid date in there when there is no valid date for the application! There's a circular argument there somewhere.

- I'm not creating a DATE column, it's a VARCHAR2 column.
- The idea is my CHECK constraint will accurately depict what's going on viz:

MyDate VARCHAR2(8) NOT NULL CHECK(MyDate = ' ' OR PROIV.ValidDate(MyDate, 'YYYYMMDD'))

That is, I think, about as intuitive as I can make it to someone reading the table definition, to me it would be clear what's happening there.

PS. (obviously) A ProIV date is not an (Oracle) SQL DATE, it has a lesser range and cannot contain a time-of-day component. My constraint can serve to prohibit those things also, thereby controlling data written from outside ProIV.

PPS. Yeah - I'm starting to think a little PL/SQL package to support various aspects of ProIV dates (and support of timestamps) would be very useful.

If a non-valid date was allowable (i.e. " "), then another column should be introduced (in this case a Y/N flag) to indicate that fact on a row by row basis.
Once again, in my opinion the introduction of implied logic into columns is dangerous (i.e. if this column is blank then do something, otherwise it's just a plain old date).

But you have to have the logic! That logic is the semantics of the application data.
If the column is blank you do have to do something.
From one point of view, "all" your alternative does is "spread" that logic across two columns and potentially allow the programmer to forget to do something!

Why have another column if you can accurately test a single column?

IMHO, your (in principle correct) suggestion of a separate column is unsatisfactory in the specific sense that every programmer/user who ever tries to use the data has to remember to interrogate the "status" column first.

What my proposal does is force the semantics into one column in such way that programmers and users hopefully cannot ignore the semantics without getting a runtime errror. Again, in my particular circumstances, I consider that more important than a "purist" database design.

And yes, an unspoken issue is that the 'YYYYMMDD' representation is important in the sense that the column can still be easily indexed correctly. And yes, that does mean the technique does not extend as readily as I would like to NUMBER data..

The "Correct" solution
=================
In fact, I think that the "correct" solution to the problem is almost certainly to use object-relational capabilities to create and encapsulate the "two column solution" into new column types such as QUALIFIED_DATE. However, that's a whole new world for all the programmers involved and I'm not sure it's a step I could reasonably propose for the project in question.
Also, we have no experience today of how, if at all, that brave new* ORDBMS world plays with ProIV. It'd be a step in the dark without a separate project up-front to sanity-check the whole idea.

* that's "new" in the ProIV sense of only ten years old..

Whoa there fella ...

The introduction of a second column would be to handle ...

What I'm actually trying to do is use a NOT NULL VARCHAR2 column to contain either a valid date or be "blank" to indicate "no date specified" or similar.

The important portion of the quote being the " or similar" part. My understanding was that you were trying to overload the column, and that the lack of data (other than "") in the column could mean more than just the fact that a date doesn't exist. The extra column that I mentioned would NOT tell you whether a valid date existed in the column, but rather what the date column in a particular row was actually being used for (i.e. to hold a valid date or something else). This is just a plain old text column that may or may not contain a date, but you're putting PRO-IV's "null' ("") as a value in the column if no date exists, rather than ORACLE's NULL. Correct ?

- I'm not creating a DATE column, it's a VARCHAR2 column.
- The idea is my CHECK constraint will accurately depict what's going on viz:

Then do that my friend. SQL is just a query language (as the name suggests). If you wish to create columns that Oracle cannot validate automatically (i.e. creating a non-date column to hold a date) then you will have to make damn sure that all applications (not necessarily just PRO-IV ones) that maintain data within that column do so correctly. As you are aware SQL has it limitations, which is why there is PL/SQL.

I'm not trying to fuel the fire here, I just think that English is a crap language (it leads to problems with interpretation sometimes), and also a Sunday morning after a Saturday night of Becks is not a good time to answer PRO-IV questions.

Regards

Shaun

Hi Richard,

You could try the following if a format check is a priority.


SELECT * FROM table WHERE
char_column(5,6) >= '01' AND
char_column(5,6) <= '12' AND
char_column(7,8) >= '01' AND
char_column(7,8) <= '31'

If SQL returns results for the checks on positions 5-8, then the first 4 positions are probably as you want them...but you could add them in as well...if you want.

This would be SQL intensive as SQL must check individual positions within char_column, but it will accomplish what you desire. At a later time, too, you could also make it dynamic.

mvh.

Thanks Ken but but I'm trying to write a CHECK constraint that recognizes valid dates so that I can, as Shaun puts it "make damn sure that all applications (not necessarily just PRO-IV ones) that maintain data within that column do so correctly"

The kind of code you suggest cannot accurately identify valid dates whereas Oracle already has code that can - so I need to use the built-in Oracle facilities.