In oracle there is the TO_DATE function which accepts at least two arguments: a field convertible to string, and a string defining the date format of the first field. This format string involves portions of date strings like 'YYYY-MM-DD HH24:MI:SS' , our standard sortable no-timezone date format that we use. But users, being users, like to enter in dates in just about any format, so we allow other formats like 'MM/DD/YYYY' and 'MM/DD/YY' (being primarily in america, we give priority to the month/day/year format, instead of the european day/month/year).
We then convert our input string into letter-number combinations using TRANSLATE so that 0-9 become 9, and A-Z become Z. Then in any_date_format we have patterns like: date_format=> 'YYYY-MM-DD HH24:MI:SS', date_pattern => '9999-99-99 99:99:99' and we select the date_format column based on the likeness of the date_pattern column, run it through to_date, catch any exceptions, and return the DATE column. --a On 8/19/07, Rod Dav4is <[EMAIL PROTECTED]> wrote: > > I'm not claiming to have invented anything new, as I was using similar > techniques some 30 years ago in another life (in the employ of a large > blue company which shall remain nameless :-[ ). Not in SQL, you > understand. A pretty thorough search didn't turn up any such use in > online SQL documentation or forums so I thought to mention it just as an > "unusual usage", to perhaps stimulate others to consider the > possibilities. > > *Q*: In what ways have others used the //'string' LIKE column-name// form? > > *Q*: Is there a name for this usage in SQL? > > *Andrew*: How does your date table work? > > -R. > > Andrew Finkenstadt wrote: > > We've used this exact technique in a table called "ANY_DATE_FORMAT" > since > > 1997... admittedly in Oracle, but the principle still applies. > > > > It's great for coalescing the number of states necessary to implement > > parsers. :) > > > > --andy > > > > > > On 8/18/07, Rod Dav4is <[EMAIL PROTECTED]> wrote: > > > >> Conventional usage is as follows: > >> > >> ... WHERE column-name LIKE "string" > >> > >> Which, of course, selects rows where the values in the named column > >> match the string, which can, and usually does, have wildcards. > >> > >> I have been using an inverted arrangement: > >> > >> ... WHERE "string" LIKE column-name > >> > >> This allows the wildcards to be in the named column of the database. > >> What possible use could this be? It's hard to even get your head around > >> it. > >> > >> > > > > > > -- > Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA > Genealogy, et Cetera: http://freepages.rootsweb.com/~dav4is/ > 480 ancestral & collateral families, mostly 17°-19° century > New England & European roots. Total population: 117,600+ > Annex: http://www.gencircles.com/users/dav4is/ > email: [EMAIL PROTECTED] > Two roads diverged in a wood, and I... I took the one less traveled > by, and that has made all the difference. > -Robert Frost > > > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > > ----------------------------------------------------------------------------- > >
