Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7

[Amir]

Hi,

I have a form used to search for records between curtain dates. In that form
the user types 2 dates, then runs the search using a 'search' button, and he
should see all the records which are between the dates typed.

The controls in the form are:
MinDateAndTime (TextBox)
MaxDateAndTime (TextBox)
RunSearch (Command Button)

I want the user to input the date/time value in the following format:
dd/mm/yyyy hh/mm/ss

so I've put the following input mask for both MinDateAndTime and
MaxDateAndTime:
00/00/0000\ 00:00:00;0;_

Now what I want to do is that each time the RunSearch button is clicked, it
will run a code that alters the view which is the record source of the form
(View is named: View1), then requery, so that the form will contain only the
records which meets the dates restriction.

The code for the RunSearch button is:
(look at the WHERE clause, the rest is just a 'fill'..)

Private Sub SearchButton_Click()
DoCmd.RunSQL "ALTER VIEW View1 AS " & _
"SELECT dbo.EVENTS.FREE_TEXT, dbo.EVENTS.mytext, " & _
"dbo.EVENTS.EVENT_ID, dbo.Z_EVENTS.Z_EVENT_ID, dbo.Z_EVENTS.MYTEXT
AS Expr1, " & _
"dbo.EVENTS.EVENT_TIME " & _
"FROM dbo.EVENTS LEFT OUTER JOIN " & _
"dbo.Z_EVENTS ON dbo.EVENTS.EVENT_ID = dbo.Z_EVENTS.EVENT_ID " & _
"WHERE (dbo.EVENTS.EVENT_TIME < " & _
"CONVERT(DATETIME, " & Chr(39) & Me.MinDateAndTime & Chr(39) & _
", 102)) AND (dbo.EVENTS.EVENT_TIME > CONVERT(DATETIME," & _
Chr(39) & Me.MaxDateAndTime & Chr(39) & ", 102))"
Me.Requery
End Sub

The problem is that after entering, for example, the following values:
MinDateAndTime: 19/12/2005 12:12:12
MaxDateAndTime: 19/01/2006 12:12:12

when I click the RunSearch button I get the following error message:
Run-time error '242':
The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.

I've tried looking for the answer in:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
but I can't understand what exactly I should put in the SQL command in order
for that to work properly, while still letting the user enter the dates in
the
dd/mm/yyyy hh/mm/ss
format and not in other formats.

I'm using Access2002 with Microsoft SQL Server 7.

Kind Regards,
Amir.

 

[Sylvain Lafontaine]

First, editing Views instead of using server filters or parameterized stored
procedures: I'm not sure if this is a good idea.

Second, if you run the command « select convert (nvarchar (10), getdate(),
102) », you will see that the result is something like 2005.12.22 and not
22/12/2005. Instead of 102, try with the 103 format or reformat
(split/relink) the parts of the string MaxDateAndTime into the proper string
format for 102.

 

[Amir]

Hello,

First of all, thanks for your answer.

I've tried to solve that by using stored function, but:
1. I'm currently working with SQL Server2000, but I will need to implement
this on SQL Server 7.0 version, so I think I could not use stored functions
there. Am I right?
2. I've tried to use the following stored function:
CREATE FUNCTION dbo.SHOW_FILTERED_RESULTS (@DaysBackToSearch Int,
@CurrentDate SmallDateTime) RETURNS TABLE
AS
RETURN (SELECT dbo.EVENTS.FREE_TEXT, dbo.EVENTS.mytext,
dbo.EVENTS.EVENT_ID, dbo.Z_EVENTS.Z_EVENT_ID, dbo.Z_EVENTS.MYTEXT AS Expr1,
dbo.EVENTS.EVENT_TIME
FROM dbo.EVENTS LEFT OUTER JOIN
dbo.Z_EVENTS ON dbo.EVENTS.EVENT_ID = dbo.Z_EVENTS.EVENT_ID
WHERE (dbo.EVENTS.EVENT_TIME >=
DATEADD(DAY,-@DaysBackToSearch,@CurrentDate)));

but that produces the following error message:
Server: Msg 170, Level 15, State 1, Procedure SHOW_FILTERED_RESULTS, Line 8
Line 8: Incorrect syntax near ';'.

Do you have any idea about how I can solve that?

I will be pleased if you post a more detailed explanation about how do you
think I should solve that problem of filtering the search results form
without using Access filters, so that all the filtering will be done at the
server.

Kind regards,
Amir.

 

[Sylvain Lafontaine]

First of all, did you try the format 103 instead of 102 in your calls to the
Convert() function?

 

[Sylvain Lafontaine]

You're right, you can't use Functions with SQL-7. However, even with
SQL2000, you should use SP whenever possible instead of functions when you
are interfacing with Access. If you want to use a Function, then you must
write an explicit SQL statement as the record source; something like:

"Select * from SHOW_FILTERED_RESULTS (" & .... & ")"

and build this string with the required parameters inside the parenthesis.
This is much less flexible than using a parameterized SP.

In your case, the error message come from the « ; » that you have added at
the end. Contrary to Access, with SQL-Server, you don't finish SQL
statements with a semi-comma.

 

[Amir]

Hi,

Thanks again for your answers.

The 103 format is working properly, but (I think) it limits the user to
dates only, without hours restriction.
On the other hand, I've given it a little thought, and actually it's a bit
more comfortable for the user not to be forced to enter the exact hours each
time, so there is no need to solve that issue by now (even though I would
have wanted to know the solution for using both date+time just because of
curiousity).

As to your second answer about the functions, I think don't understand it
completely.
Currently I can think of 3 (similar) methods of doing what I wish to do
using Access and SQL Server 7 (e.g. without using stored functions):
1. Use parameterized stored procedure which will alter the View that is used
as the row source of my search results form.
2. Use Access DoCmd.RunSQL command in order to do the same (alter the view
which is used as the row source for the results form).
3. Use SQL sentence as the row source of the search results form (instead of
a view), and change that SQL sentence each time the user clicks the 'search'
button.
(If I am not wrong) Both 3 options should 'create' the correct query, so
that after they run, I might show the form and run a Requery command to show
the results.
I think I'll use the 2nd or the 3rd option since I'm quite new to stored
procedures.

Am I wrong? Is there something bad with one of these solutions? Are there
other solutions assuming I have to use SQL Server 7?

At your first answer you've mentioned that:
'editing Views instead of using server filters or parameterized stored
procedures: I'm not sure if this is a good idea.'
I don't understand how I can use parameterized stored procedures to solve my
problem, because my problem requires that a value will be returned (e.g. a
table), and such things are done by using functions, aren't they?
Is there a way to solve my problem by using stored procedures? How?

Kind Regards,
Amir.

 

[Sylvain Lafontaine]

For parsing a string to a date/time, the 103 format doesn't limits the user
to dates only and will correctly parse a time. See the BOL on CAST and
CONVERT topic for more info.

For your second question, SP are not void functions: they will returns a
resultset (or recordset in DAO or ADO language) if they end with a SELECT
statement. In the case of SP with parameters, you have two possibility for
calling it:

1) you can set the record source to the name of a SP, set the Record
Source Qualifier to dbo and put the list of parameters in the Input
Parameters property. Check previous posts in this newsgroup for more
details.

2) a second possibility is to set the Record Source property to an EXEC
string that will call the SP; for example:

EXEC MyStoreProcedure 100, 'aaaa', ....

This last method is the most easier to use for beginners. However, with SP,
you must be very careful about the use of the NOCOUNT property. Usually,
you should always set the option to ON at the beginning of each SP that you
intent to call for a returning a resultset:

SET NOCOUNT ON
...

Altering Views might seem a good idea but it's not in the case of a
multi-users application.

 

[Amir]

Sylvain,
I am grateful for these wonderful detailed answers.
I didn't think about the multi-user problem that can be caused becuase of
using views, and now I've finally succeeded in retrieving records from my DB
to the form using the 1st method you've suggested (with using Input
Parameters).

I have one more question about the dates issue:
Here is the where clause i'm using in my 'CREATE PROCEDURE' command, in
order to create the SP which is used as the record source for my search
form:
WHERE (dbo.EVENTS.EVENT_TIME >= DATEADD(DAY, - (@DaysToSearch), GETDATE()))
AND

dbo.EVENTS.FREE_TEXT LIKE @SearchedText



The purpose is to let the user search for a string (This part works fine) in
the time of @DaysToSearch days before the user clicked the 'search' button.

The problem is that I'm not sure it's OK to use the GETDATE() function over
there.

I remember that I've read once that doing such things is wrong since the
server compares the parameter values to the date of the creation of the SP,
instead of comparing them to the date of calling the SP. Is that true?
How can I ensure that the server looks x days back from the moment I press
the 'search' button, and not x days back from the moment I created the SP?

Regards,
Amir.

 

[Sylvain Lafontaine]

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

 

[Sylvain Lafontaine]

The use you are doing with the GetDate() function is fine.

However, if you have created an index on the EVENT_TIME field, then it's
better to use a variable for storing the value of the searching date because
calling a function in a where clause will force the optimizer to always do a
table or clustered index scan instead of using the non-clustered index on
EVENT_TIME:

declare @dt datetime
set @dt = DATEADD(DAY, - (@DaysToSearch), GETDATE())

WHERE (dbo.EVENTS.EVENT_TIME >= @dt

The fact that the GetDate() and the EVENT_TIME also have a time part might
cause you some trouble if you don't set them to 0 before doing the
comparaison.

 

[Amir]

Hi Sylvain,

Thank you for that important tip. The speed of this specific search is very
important, so I'll use an index for the time column, and a variable just as
you suggested.

As to the time part issue, I'm not completely sure I understand what you
mean. Guess the search date/time (e.g. the date/time when the user presses
the 'search' button) is 25/12/2005 03:00:00 and the record i'm looking for
has the date/time of 24/12/2005 01:00:00. In such a case, if the user
chooses to serach 1 day back, he won't get results. That's fine with me.
If you meant that in such a case it's problematic that the user doesn't get
results, and that he should get all the records starting from records with
date/time value of 24/12/2005 00:00:00, then this doesn't make such a
problem in my case.
If this is not the problem you meant in your last paragraph, or there are
other problems you can think of, I will be pleased if you write them.

Kind Regards,
Amir.