By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

Search Between Times

P: n/a
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)
From Table A


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

Apr 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(ba**************@gmail.com) writes:
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...


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, es****@sommarskog.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
Apr 22 '06 #2

P: n/a
>> I have some monthly data which is being encoded as Varchar...Say for the month of January 2005 it would look like '200501'. For each of my month I have a columns which gives me datetime and volume of the job. <<

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.

Apr 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.