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

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" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23S0AjeyBGHA.1092@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "Amir" <agamy@xxxxxxxxxxxx> wrote in message
> news:eEjSMfqBGHA.4076@xxxxxxxxxxxxxxxxxxxxxxx
>> 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.
>>
>
>


.

Loading