473,327 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Search Between Times

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
2 1119
(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
>> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Phil Powell | last post by:
The table already has a fulltext index and from there I can use the MySQL fulltext search query to get results as well as the relevancy score. The problem I have is that MySQL has a default...
10
by: Anand Pillai | last post by:
To search a word in a group of words, say a paragraph or a web page, would a string search or a regexp search be faster? The string search would of course be, if str.find(substr) != -1:...
4
by: Ken Fine | last post by:
I'm looking to find or create an ASP script that will take a string, examine it for a search term, and if it finds the search term in the string, return the highlighted search term along with the...
5
by: Abby Lee | last post by:
My code does what I want (works unless there is a lot of volume...works for this month cause not a lot of items marked paid yet...) but the page times out for last month because there is just so...
3
by: Alastair | last post by:
Hello guys, I've been building a search facility for an intranet site I'm part of developing and we've been building a search engine using Index Server. It mostly works, however there have been...
28
by: joshc | last post by:
If I have an array of data that I know to be sorted in increasing order, and the array is less than 50 elements, and I want to find the first element greater than a certain value, is a simple...
60
by: Julie | last post by:
What is the *fastest* way in .NET to search large on-disk text files (100+ MB) for a given string. The files are unindexed and unsorted, and for the purposes of my immediate requirements, can't...
32
by: tshad | last post by:
Can you do a search for more that one string in another string? Something like: someString.IndexOf("something1","something2","something3",0) or would you have to do something like: if...
2
by: Alphonse Giambrone | last post by:
Is there a way to use multiple search patterns when calling Directory.GetFiles. For instance Directory.GetFiles("C:\MyFolder", "*.aspx") will return all files with the aspx extension. But what if...
1
Merlin1857
by: Merlin1857 | last post by:
How to search multiple fields using ASP A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.