DateTime Comparison

[rarubio1]

I am working on an application that allows a user to select a time value from a combo box. A sample of the values in the combo box are as follows: 6:00 A.M., 12:00 P.M., 8:30 P.M., etc…. The value selected by the user is then stored in the database as a string (varchar) value. This was not a big deal at first since I wasn’t doing any calculations or time comparisons that involved these values. They were simply being displayed.
Now I have been asked to take the time value entered by the user and check to see if it falls within a specific time range. I have to somehow convert the string value to a DateTime value, for example 7:30 A.M. and check if it falls within the 5:30 A.M. to 2:30 P.M. range.
Can anyone please tell me how to accomplish this task.

The program is a Visual Basic program and the database is SQL SERVER 2000.

Thanks in advance,
RR

 

[chiph]

Suggestion: Try and get the column type changed in SQL Server to a datetime. It will make your life much easier later.

The function you want to use in VB6 is DateDiff:

DateDiff(interval, date1, date2[,firstdayofweek[, firstweekofyear]])

"interval" is a string that reprsents the units you want the results in. For your example, "n" represents minutes. If you get a negative result, you know that date1 is before date2.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[rarubio1]

I don't have the option to change the column to a DateTime datatype. That's why I need to know how to convert string 8:20 A.M. into a time value. I tried CDate() but that did not work. Thanks anyways.

RR

 

[Schroeder]

This seems to work for me

    blnInRange = CDate(txtUserInput) >= "5:30 AM" And CDate(txtUserInput) <= "2:30 PM"

 

[rarubio1]

Thanks, I will try it and let you know if it worked.

RR

 

[Polariz666]

I'd like to hijack this thread slightly because i'm looking for something similar.

I have two calendars, which I want to pass the dates into an SQL query. I want to do this in a loop, from the first date selected, for every day till the date selected in calendar 2. I'm pretty sure I need to use datediff to get the values however im not sure how to increment the loop every time to reach the final date. In essence, I need to add a day everytime until the value in the loop equals the calendar2.value.

Any help appreciated

 

[Milia]

You can convert back to time in your stored procedure.

Select * from tblName where cast(right(strdate,8)as smalldatetime) between @param1 and @param2


Milia