Comparing Date Strings 2

[anastasia]

Hi, I am using the following code:

<% IF bookingdate < StartDate THEN
errorForm &quot;Booking Date entered is before Start of Season&quot;
END IF %>

<% IF bookingdate > EndDate THEN
errorForm &quot;Booking Date entered is after End of Season&quot;
END IF %>

What I am comparing is Dates held within a database and a date entered by the user. The date entered by the user is a text string as 13/04/00 this is not formatted as a date. The date held in the database is set to text and is entered in the same way. I am comparing the date entered by the user to the StartDate and EndDate in the database but get incorrect results. I need something that will convert both strings maybe to a value then compare these.

Any Suggestions welcome.

Thanks.

 

[foxbox]

Mmmmmmmmmmmm. In fact you are telling us that you are comparing textstrings (yeah for people they look like dates, but they are text input and text fields in the database). This is perfectly oke, but for GT/LT compariosons its better to format the string into:
yyymmmddd.
The classic problem is: you are asking your users to enter the date, and they will type ddmmyyyy (in my country) of mmddyyyy, etc. You can structure that by presenting 3 <SELECT>'s....
br
Gerard
(-:

| Do it!
|
V

 

[rhyswilliams]

Try using the dateserial function to convert you string values to dates:

YearVar = right(StartDate ,2)
MonthVar = left(StartDate ,2)
DayVar = mid(StartDate ,2,2)

StartDatedate = dateserial(YearVar,MonthVar,DayVar)

I hope this helps...

 

[anastasia]

rhyswilliams I have tried your code given above but get this error:

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'DayVar'

/Pc/submit.asp, line 122

 

[LnOrS]

Well,

I think the problem is not only with the day but also with the month!! I think rhyswilliams forgot the separators and the date format dd/mm/yy.

YearVar = right(StartDate ,2)
DayVar = left(StartDate ,2)
MonthVar = mid(StartDate ,4,2)

StartDatedate = dateserial(YearVar,MonthVar,DayVar)

This code only works if date format is dd/mm/yy
You can also use

DateArray = split(StartDate,"/", -1, 1)
StartDatedate = dateserial(DateArray(2),DateArray(1),DateArray(0))

Regards,
Luís Silva

 

[LnOrS]

Sorry,

The last code line should be:

StartDate = DateSerial(Val("20" & DateArray(2)),Val(DateArray(1)),Val(DateArray(0)))

DateSerial arguments should be integer, so you must convert strings into integers, you can do that with Var function.

If you prefer to use rhyswilliams (modified) code be sure of this fact and that the year must be an integer between 100 and 9999.

Regards,
Luís Silva

 

[AgentM]

Maybe somebody can help me here
In a SQL query I have to compare a date field in Access (d1)with the current date (d2).

The format of both is in mm/dd/yy due to
d2 = FormatDateTime( Now , 2).The date field in Access is also stored in the same format.

How can I use d2 to compare with d1 in a Sql Query

Thank you

 

[vasah20]

when you pass in d2, surround it by # # signs.

ex:
rs.open &quot;SELECT * FROM tblName where d1 < #&quot; &d2& &quot;#&quot;, cn

does this help
leo

 

[AgentM]

Yes that worked
Thank you