"Rico" <me@you.com> wrote in message news:1DTTh.34$VF5.16@edtnps82...
>I have some SQL with the following line in it;
>
> WHERE CONVERT(varchar(8), dbo.tblAppointments.fldAppointmentDate, 112)
> BETWEEN CONVERT(varchar(8), CONVERT(Datetime, '4/2/2007'), 112) AND
> CONVERT(varchar(8), CONVERT(Datetime, '4/3/2007'), 112)
>
> When I leave this as a where statement I get nothing when I execute the
> SQL. When I change the WHERE to a Group By in the Diagram pane, I get the
> two rows that are the expected result. I have two questions really, 1).
> can anyone give me insite on why this is happening and 2) if there is a
> reason for this strange behavour, is there any way I can get the expected
> result without returning a row for each AppointmentDate (supposed to only
> show clients).
>
> Any help would be greatly appreciated.
>
> Thank You!
> Rick
>
>

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:O%23H4P9hfHHA.5052@TK2MSFTNGP06.phx.gbl...
> "Rico" <me@you.com> wrote in message news:1DTTh.34$VF5.16@edtnps82...
>>I have some SQL with the following line in it;
>>
>> WHERE CONVERT(varchar(8), dbo.tblAppointments.fldAppointmentDate, 112)
>> BETWEEN CONVERT(varchar(8), CONVERT(Datetime, '4/2/2007'), 112) AND
>> CONVERT(varchar(8), CONVERT(Datetime, '4/3/2007'), 112)
>>
>> When I leave this as a where statement I get nothing when I execute the
>> SQL. When I change the WHERE to a Group By in the Diagram pane, I get the
>> two rows that are the expected result. I have two questions really, 1).
>> can anyone give me insite on why this is happening and 2) if there is a
>> reason for this strange behavour, is there any way I can get the expected
>> result without returning a row for each AppointmentDate (supposed to
>> only show clients).
>>
>> Any help would be greatly appreciated.
>>
>> Thank You!
>> Rick
>>
>>
>
> Exactly what did you use in the GROUP BY clause? GROUP BY doesn't perform
> any restriction like WHERE does so there is no direct comparison.
>
> Assuming fldAppointmentDate is a DATETIME or SMALLDATETIME column then
> your WHERE expression is very ill-constructed. In a range query like this
> it's important to avoid unnecessary type conversions. It's also better to
> rely on ISO date formats rather than regional formats. Example:
>
> ... WHERE fldAppointmentDate >= '20070402'
> AND fldAppointmentDate < '20070404'
>
> Avoid the Query Design interface if you can because it will mangle your
> code and anyway has far too many limitations. As for the "fld" and "tbl"
> prefixes... maybe the less said about that the better :-(
>
> Hope this helps.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
>
>
>

On 14 Apr, 01:47, "Rico" <m...@you.com> wrote:
> Hi David,
>
> Yea, the tbl and fld are not my standard, but I'm not signing the cheques so
> that's the way it has to be! ;)
>
> I know the group by doesn't perform any comparison, but it will create the
> "HAVING" clause. I find the designer easier to read at some points, but I
> do know what you're saying. My question is, why would the results change,
> when the basic filter / criteria is the same.
>

> With regard to the dates, I'm also storing the times as well, so '3/3/2007
> 2:30:00 PM' is not between '3/3/2007 12:00:00 AM'and '3/3/2007 12:00:00 AM',
> so I thought that would be as good a way as any to handle it. What is the
> best way to handle this type of comparison?
>

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1176538658.035031.181810@l77g2000hsb.googlegroups.com...
> On 14 Apr, 01:47, "Rico" <m...@you.com> wrote:
>> Hi David,
>>
>> Yea, the tbl and fld are not my standard, but I'm not signing the cheques
>> so
>> that's the way it has to be! ;)
>>
>> I know the group by doesn't perform any comparison, but it will create
>> the
>> "HAVING" clause. I find the designer easier to read at some points, but
>> I
>> do know what you're saying. My question is, why would the results
>> change,
>> when the basic filter / criteria is the same.
>>
>
> Without seeing some code to reproduce the problem I can't say. It
> depends what you grouped by and what string conversions you used. As I
> said before, it's better not to manipulate dates as strings.
>
>
>> With regard to the dates, I'm also storing the times as well, so
>> '3/3/2007
>> 2:30:00 PM' is not between '3/3/2007 12:00:00 AM'and '3/3/2007 12:00:00
>> AM',
>> so I thought that would be as good a way as any to handle it. What is
>> the
>> best way to handle this type of comparison?
>>
>
>
> Avoid using BETWEEN with dates. Use >= and < instead - it works much
> more naturally for dates and times. Using the example you described:
>
> CREATE TABLE t (dt DATETIME PRIMARY KEY);
> INSERT INTO t (dt) VALUES ('2007-03-03T14:30:00');
>
> SELECT dt
> FROM t
> WHERE dt >= '20070303'
> AND dt < '20070304';
>
> dt
> -----------------------
> 2007-03-03 14:30:00.000
>
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>