Date Validation

I have an application running on a SQL Server Database.  There's a large number of date fields scattered about through out the forms. I've put Coldfusion validation on them all, and am using cfqueryparam (date) in all my insert/update queries and the SQL Fields are all "date" as well. Now, the client says "We just wanna enter a month and year when we feel like it whenever a specific day doesn't apply". Obviously, "2/1998" doesn't validate. Suggestions?? - hopefully without having to redesign half the database.

>Coldfusion validation on them all Have you got your own date validation function? Or have you got isdate() all over the place? ----- Excess quoted text cut - see Original Post for more -----

Yes, default the date to:  MONTH/01/YEAR I have an application running on a SQL Server Database.  There's a large number of date fields scattered about through out the forms. I've put Coldfusion validation on them all, and am using cfqueryparam (date) in all my insert/update queries and the SQL Fields are all "date" as well. Now, the client says "We just wanna enter a month and year when we feel like it whenever a specific day doesn't apply". Obviously, "2/1998" doesn't validate. Suggestions?? - hopefully without having to redesign half the database.

Yea, you could do something before your queries run that check the form scope for your date values, and check if isDate().  If not, check if you can pull out the month and year separate (maybe just use listGetAt(1, form.date1, '/') ) and then re-write that form value to be the 1st? Chris Yes, default the date to:  MONTH/01/YEAR I have an application running on a SQL Server Database.  There's a large number of date fields scattered about through out the forms. I've put Coldfusion validation on them all, and am using cfqueryparam (date) in all my insert/update queries and the SQL Fields are all "date" as well. Now, the client says "We just wanna enter a month and year when we feel like it whenever a specific day doesn't apply". Obviously, "2/1998" doesn't validate. Suggestions?? - hopefully without having to redesign half the database.

> Have you got your own date validation function? > Or have you got isdate() all over the place? Standard cfform validation: <cfinput name="II_D_DC5"     value="#dateformat(getapp.II_D_DC5,'mm/dd/yyyy')#"     class="II_D_DC1"     type="text"     message="Please enter a valid date"     validateat="onSubmit"     validate="date" /> > Yea, you could do something before your queries run that check the form > scope for your date values, and check if isDate().  If not, check if you > can pull out the month and year separate (maybe just use listGetAt(1, > form.date1, '/') ) and then re-write that form value to be the 1st? Just got off the phone with them - if they don't associate a specific day with the date, then for legal reasons there *can't* be specific day with it - it can't even default to the 1st. They may even want to enter "sometime in 1979"! That pretty much changes everything. Looking like I'll have to change all the SQL Date fields to accept text and just let them enter whatever the heck they want, since that's what they're asking ("1/23/2006" or "2/1998" or "sometime in 1987"!). Whatever!! - this is outside the original spec that *specified* specific date formats, so I get to bill them more now. They're lawyers, so they have unlimited pockets anyway, no? Clients! They'll walk all over you with "feature creep" if you let them, huh?

Les Mizzell wrote: ----- Excess quoted text cut - see Original Post for more ----- My preferred solution for that problem is to store both a start date and an interval, i.e. store '1/1/1987' and '1 year'. That way you can still do range searches on the date. Jochem

Make sure the month is in the 1-12 range and make sure it's a valid year before you submit?  You could also just throw in 1 for the day if it is not otherwise noted... Eric I have an application running on a SQL Server Database.  There's a large number of date fields scattered about through out the forms. I've put Coldfusion validation on them all, and am using cfqueryparam (date) in all my insert/update queries and the SQL Fields are all "date" as well. Now, the client says "We just wanna enter a month and year when we feel like it whenever a specific day doesn't apply". Obviously, "2/1998" doesn't validate. Suggestions?? - hopefully without having to redesign half the database.