Hi I'm new to SQl..

I have some monthly data which is being encoded as Varchar...Say for
the month of January 2005 it wud look like '200501'. For each of my
month I have a coloums which gives me datetime and volume of the job.

This is what I'm doing

Select

Month,
Time ,
Sum(Volume)

Where (( Month like '200501%' ) and (Time between '2005-01-01
02:00:00.000' and '2005-01-31 06:00:00.000))

group by month, time

order by month

PRoblem: What I want is all the volume between 2am and 6am on every day
of the month?

The above query naturaly will give me everything between first and last
of the month...

Any help is appreciated...

Balaji

(balaji.balaraman (AT) gmail (DOT) com) writes:
convert(char(8), Time, 108) BETWEEN '02:00:00' AND '06:00:00'

You find convert under the topic CAST and CONVERT in Books Online,
where you can see the different format codes (108 in this example) that
are available.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Wrong on two levels. Temporal data needs temporal data types. And if
you know the string is fixed length, why make it VARCHAR(n)? Do you
really want bad data? Did you write a proper CHECK() constaint to
prevent things like '200613'? I would bet not.

The right way is to put the range pairs into a Reporting Ranges table.

CREATE TABLE ReportRanges
(start_date DATETIME NOT NULL PRIMARY KEY,
end_date DATETIME NOT NULL,
CHECK (start_date, end_date));

INSERT INTO ReportRanges
VALUES ('2005-01-01 02:00:00.000', '2005-01-31 06:00:00.000);
etc.

Use a spreadsheet to build the data, then use a simple join. 36525
rows will cover ten years of reporting.