Gossamer Forum
Home : Products : DBMan SQL : Discussion :

date formats

Quote Reply
date formats
Has anyone found a good way to deal with the problem of date formats in text fields? You can populate a field with a date formatted in a particular way, but users inevitably try to enter a date formatted some other way, which prevents the data from being properly entered into the database. Are there any tricks/tools/techniques that I'm missing (as you might have guessed, I'm thinking specifically about those can work with DBManSQL), or is this just a frustrating fact of life?

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Quote Reply
Re: [hennagaijin] date formats In reply to
Why not use a regular expression to control what the user can add to your database.

I use the following date/time format -

2002-10-30 23:04:52

and the following regular expression -

[0-9]{4}\-[0-9]{2}\-[0-9]{2}\s[0-9]{2}\:[0-9]{2}\:[0-9]{2}

which means that the date time format must be xxxx-xx-xx xx:xx:xx or the user cannot add it to the database. I'm new to regular expressions so there is probably a better way to write it but the above example seems to work fine.

Take a look at page 86-88 of the new DBMan SQL manual.

Simon.
Quote Reply
Re: [jai] date formats In reply to
Thanks. That is what I ended up doing. I once encountered a really great function (in PHP, I think?) that would automatically convert a date from almost any format to the standard MySQL format. I wonder if anything like that exists in perl... Anyway, a regex certainly gets the job done, if not as elegantly as might be ideal.

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Quote Reply
Re: [hennagaijin] date formats In reply to
[12]\d\d\d\-(0?\d|10|11|12)\-([012]?\d|30|31) ([01]?\d|20|21|22|23):[0-5]?\d:[0-5]?\d

Is a bit more strict, but it still allows things like 2002-11-31 (and it introduces a new millennium bugWink)

Jasper

http://www.bookings.org