[thelist] Dropping a view in SQL Server

rudy r937 at interlog.com
Tue May 8 19:01:12 CDT 2001


>Yes actually... but i don't want the error....

hi marios

okay, you can ignore the error   ;o)

you only have to declare a view once, it's not like you're getting an error
on a select statement which you plan to run over and over again...

>   Select avg(value) as a
>       from theview
>     where theday between mm/dd/yyyy and mm/dd/yyyy
>         and instrument = X
>     group by theday
>
> And the problem is the "theday between mm/dd/yyyy and mm/dd/yyyy"
> comparison, because 'theday' is char. How can I make the comparison
> to work?

use character strings for your dates

    where theday between 'yyyy.mm.dd' and 'yyyy.mm.dd'

check http://msdn.microsoft.com/library/psdk/sql/ts_ca-co_2f3o.htm again

CONVERT type 102 specifically means the ansi format with centuries and
periods as separators

one of the reasons i prefer the ansi standard format is that you can do
range tests like the above on the full date, which you cannot do with other
*cough*american*cough* date string formats (e.g. 12/13/2000 comes "after"
01/01/2001)

also, i mentioned before but you will see why only after you get some
results back, you also have to add thedate to the select list, because if
you don't, you will end up with a result set like this --

  A
  ---------
  40.2
  45.6
  23.2

okay, quick, which average is for which date?

unless, of course, you don't want daily averages -- but that is an entirely
different query!

if you want the overall average for a number of days, just remove the group
by clause


rudy






More information about the thelist mailing list