HI All,

I have a field deifned in my table as datetime.

I wisht to retrieve from the client, 2 dates and then search for records that fall between these two dates.

How do I validate my dates entered by the client.

cheers

Originally Posted by http://dev.mysql.com/doc/refman/4.1/en/datetime.html

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
• As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

As you can see, working with datetime is really a breeze. If you want to validate between dates you can use the BETWEEN operator (function, whatever) like so...

SELECT * FROM mytable WHERE col_datetime BETWEEN 'timestamp1' AND 'timestamp2';

ok thats wicked but how do i format from my input field on my web page.

cheers for you time

In any one of the above mentioned ways. The point of that blurb was to let you know, essentially, that MySQL accepts a relaxed or strict format for datetime values.

Why not have 3 select fields, each populated with day, month and year values. Then, after the user has submitted, you can stick 'em together with a simple script and thrust them into your DB.

Originally Posted by robsmith

Why not have 3 select fields, each populated with day, month and year values. Then, after the user has submitted, you can stick 'em together with a simple script and thrust them into your DB.

Because that is redundant and backwards to the way MySQL was designed.

The more you can use MySQL's functions instead of writing your own the easier your life is going to be.

Yeah i absolutley understand the need to get the SQL to do as much work as possible, but I fail to see how you can effectively validate user's input with Mysql. Are you saying that it can return the user and say "look dude, you didn't use the correct format"?

ASP makes this very easy with a customizable datetime control (at least I believe it should be nearly the same as ASP.NET).

well not quite

the thing to do especially when dealing with date and time input is to ensure the the user is only able to input valid data.
eg;
Never use a text box for collecting dates, unless you want to have a whole raft of if statements to work out what someone typed in.

As the programmer you should be in control of what is allowed or not and how the requesting form is presented to the user.
That way there is less need for validation of the user input.

You caught me assuming (although I'm still not sure as I could not find anything like this for ASP)...

http://www.basicdatepicker.com/

This is for ASP.NET there may or may not be something similiar for ASP.OLD. This will allow you to control the format of the date without the user having to put it in a special way.

Nice, but i thought i would bring this thread back to the gutter with some bloated, old, horrible ASP

http://www.freevbcode.com/ShowCode.asp?ID=6957

Originally Posted by robsmith

Nice, but i thought i would bring this thread back to the gutter with some bloated, old, horrible ASP

http://www.freevbcode.com/ShowCode.asp?ID=6957

Sweet!