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

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

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


"Amir" <agamy@xxxxxxxxxxxx> wrote in message
news:%23z0ckM0BGHA.344@xxxxxxxxxxxxxxxxxxxxxxx
> 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