Validating Dates in ASP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Validating Dates in ASP

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-26 : 12:53:18

I've been using the ASP function

If isdate(datevariable) Then

to verify that a user input date is valid before coding it into an SQL query

WHERE datediff(second,'july 8 ',Q.StartDate) <0

Notice july 8 is a valid date, but SQL will (and rightly) throws up a

"Syntax error converting datetime from character string"

Any suggestions for a test for a better ASP valid date test which will avoid this SQL error?

SamC

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-26 : 12:59:21

Why not reformat the datevariable into a format that SQL will understand (i.e. 07/08/2002)? I am assuming you are using the datevariable to enter into the WHERE clause.

strSQL = strSQL & "WHERE datediff(second," & datevariable & ",Q.StartDate) <0 "

I'm not sure this is exactly addressing your question. If you can provide us with you code, strSQL, then we can address your issue better. Are you calling a SP or writing the SQL in your page, etc.

Jeremy

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-26 : 13:34:16

You're right in all your assumptions about the implementation.

I posted the WHERE clause in my question, it's not a stored procedure, it's an SQL string executed in a

dataconn.execute(SQLstring)

in ASP. The trick is to validate as much of the user input SQL conditions as possible to post back a user friendly response about what isn't quite right.

Formating the date string in ASP before inserting into the query might be the way to go - if an error occurs during the formating, then it's an invalid date. Go tell the user.

I'll try it. I'd appreciate feedback if there are holes in this logic or a better approach is known.

SamC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-26 : 16:37:04

You can also use some client-side JavaScript to validate dates before you even post the form. It's easy because there is a Date object in JavaScript, and constructing one is a piece of cake. If the date value is invalid, the Date object won't initialize (it will remain null/false). You can also perform other types of validation too, like checking numeric values and/or character patterns.

This is a Microsoft link for JScript:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/js56jslrfJScriptObjectsTOC.asp

But the Date object works the same way in JavaScript. Also check these sites, several of them have date/data validation code that runs on the client end:

www.4guysfromrolla.com
www.15seconds.com
www.asp101.com
www.aspalliance.com

If you need more help drop me an email.

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-27 : 13:38:30

Everyone,

Thanks for the feedback. For the moment, I'm stuck with server-side validation (I'm aware it's a bad use of resources and there are plenty of client side methods. I'll begin client side development later this year).

The validation solution I chose for user date/time input in ASP before building the WHERE IS

dateinput = Cdate(Cstr(dateinput))
If Err.Number = 0 Then
SQL = SQL & "WHERE ....."
Else
strInputFieldErrorMessage = "Bad Date/Time input = " & dateinput
End IF

The ASP Cdate formats the string "July 8 3PM" as 07/08/2002 03:00:00 PM . Both the user input and resultant strings are valid dates, but SQL accepts only the 2nd form. The first form doesn't include the year, but the ASP isdate() function doesn't flag that as an error.

SamC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-27 : 14:37:07

What about using the FormatDateTime() function to convert it into a more standardized date format? SQL Server is not very forgiving of the format of date values passed to it, you're better off having the ASP code clean it up.

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-27 : 16:46:36

Seemed the formats were the same to me.

Cdate(Cstr(mydate))
and
FormatDateTime(mydate)

Give the same result.

SamC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources