a couple of questions

Results 1 to 2 of 2

Thread: a couple of questions

  1. #1
    dates Guest

    Default a couple of questions

    I capture date in one field and use isdate to check it.<BR>1)Should I separate this into 3 fields in database table?<BR>2)Will I have trouble with queries if someone enters date with "/" and somebody else with "-" and use different formats?<BR>3)What is the easiest way to put a constraint , like I want 18 - 80...(some jerks enter 1-1-1900 or 1-1-2001)<BR>4)Where can I find query build that helps me build queries like, group by month or day or year or dates?<BR>Thank you,<BR>Luis

  2. #2
    Join Date
    Dec 1969

    Default RE: a couple of questions

    Dates are really nasty. I&#039ve found the best approach is not to let the user simply enter a date in any old format - give them 3 fields, and make it very clear which are which. Then, when you&#039ve got the form data on the server-side, reconstruct the date from those 3 fields. This way, you know exactly what format it&#039s in before it goes into the database.<BR><BR>You should represent dates in your database as single fields of the appropriate type - this makes it easier to perform date-related queries. When creating a SQL statement that features dates, the best format to use is {d &#039yyyy-mm-dd&#039} eg:<BR><BR>INSERT table (char_field, date_field) VALUES (&#039string&#039, {d &#039 2000-01-01&#039})<BR><BR>The advatage of this date format is that it&#039s always interpreted in the same way, regardless of localisation settings. There is a similar format for timestamps: {ts &#039yyyy-mm-dd hh:mm:ss&#039}.<BR><BR>If you have 3 fields for the user to enter the date, validating the year becomes easy using normal JavaScript techniques.<BR><BR>GROUP BY queries of the sort you mention are a little tricky. Remember that you can group by an expression, eg:<BR><BR>SELECT char_field, date_field FROM table GROUP BY MONTH(date_field), char_field, date_field<BR><BR>Remember that any non-aggregated columns in the SELECT list must be included in the GROUP BY clause.<BR><BR>HTH<BR><BR>Dunc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts