|
Hi I am trying to find out how many records I have between two time periods. I have the following code:
SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE (Scan_Time between '9/29/2008 9:00:00 AM' AND '9/29/2008 09:59:59 AM')
This will work and give me the number of records that are between the specified time ranges. However, I will be running this query each day and would like to not have to go in each day and specify the date. I will be clearing the database each day and thought that I could just remove the date portion, however, when I do this there are no records reported.
I am new to SQL and any help would be greatly appreciated
Thanks
.NET Framework Data Provider for SQL Server Mobile Edition
version 3.00.000
|
|
|
|
|
You could use DATEPART function and perhaps even GROUP BY hour (again by using datepart) if needed. This way you would get all counts for all hours in single statement.
The need to optimize rises from a bad design
|
|
|
|
|
Thanks for the idea Mika. I had thought about using DATEPART, but was not sure if I would be able to shorten the time period if I needed to i.e. between '9:50:00 AM' AND '09:59:59 AM'
|
|
|
|
|
You're welcome. Just use hh for the first parameter and use the DATEPART function in either WHERE clause or GROUP BY.
The need to optimize rises from a bad design
|
|
|
|
|
Thanks that worked for reporting at 1 hour intervals. Any idea how I could search between '9:50:00 AM' AND '09:59:59 AM'
|
|
|
|
|
If you want to use 10 minutes interval, you could use a trick like this:
SELECT (DATEPART( minute, GETDATE()) / 10)
It's not pretty, but gives you full 10 minutes (0-5)
The need to optimize rises from a bad design
|
|
|
|
|
create a stored procedure and make the user/app calling it pass in the dates they wanted to count between. or if you just wanted to do 2 queries datepart with MIN in place of HH would work the same, mm is month as i remind my self every so often when i use date part to mess with minutes.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Thanks for the help. Using the datepart and using a union to have 2 queries appears to work.
Thanks for the help
SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE datepart(hh,Scan_Time) between 09 and 09 union SELECT count (Scan_Time)As btw9to10 FROM main_Table WHERE datepart(mi,Scan_Time) between 50 and 59
|
|
|
|
|
listen,
check functions:
dateadd(),getdate(),datediff()
will do fine 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
hi to all,
required connection string in asp for sql server 2005 express
its urgent.
please help me out.
i tried many... they are
1. objConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=ip address;UID=kjkl;PWD=jhjk"
2.objConn.ConnectionString = "Provider=SQLNCLI;Server=ip address\SQLEXPRESS;Database=645654;UID=321321;PWD=23164;"
3.objConn.ConnectionString = "Provider=SQLOLEDB.1;UID=321321;Password=mypassword; Initial Catalog=mydatabase; Data Source=.\SQLExpress"
4. 'DSN="Driver={SQL Server};Server=ip address;Database=cfsdf;UID=35131;PWD=23131"
nothing works
help me plz
thanks
|
|
|
|
|
This issue is answered several times.
Even in this forum.
Even within few days.
So, scroll questions down a bit in this forum and you may find what you need.
The need to optimize rises from a bad design
|
|
|
|
|
i did wat u said. but when i'm saying that i tried many it means i search many and even this forum also. And i found nothing helpful.
so help me.
|
|
|
|
|
There are several articles in BOL how to construct a connection string. Also there's a separate ConnectionStringBuilder -class for this purpose. And this site has been referred many times: http://www.connectionstrings.com/[^]
If you have a valid connection string, but you cannot connect to SQL Server for some reason, provide the corresponding error message along with the connection string you used (of course after googling that it isn't any common problem). We may then be able to help you to locate the problem.
The need to optimize rises from a bad design
|
|
|
|
|
listen,
create a *.udl file in your desktop. check connection_string from there.
ok? 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi
I have written a stored procedure as follows:
ALTER PROCEDURE dbo.VehiclesInsert
(
@TypeId int = 4,
@Reg varchar = 15,
@Mileage decimal = 9,
@Price money = 8,
@Fuel int = 4,
@Colour varchar = 80,
@BodyShape int = 4,
@Doors int = 4,
@Warranty int = 4,
@MoreInfo text,
@RegLetter int=4,
@CreatedBy int=4
)
AS
INSERT INTO Vehicles
(TypeId,Reg,Mileage,Price,Fuel,Colour,BodyShape,Doors,Warranty,MoreInfo,RegLetter,CreatedBy)
VALUES
(@TypeId,@Reg,@Mileage,@Price,@Fuel,@Colour,@BodyShape,@Doors,@Warranty,@MoreInfo,@RegLetter,@CreatedBy)
RETURN
Although I have defined the Reg variable as Varchar 15, it only writes the first letter into the Reg field of my DB. I am using ASP.NET and c# to call the sproc - I even tried hard-coding the parameter value e.g:
objCmd.Parameters.Add("@Reg", "Test Reg");
However it still only adds "T" into the Reg field. I can manually add Test Reg into the field in the DB table so the field lengths all seem to be ok.
I'm very new to SProcs so please excuse me if I'm missing something obvious!
Thanks
Lorna
|
|
|
|
|
try make this changes:
.<br />
.<br />
.<br />
@Reg varchar (15),<br />
.<br />
.<br />
.<br />
@Colour varchar (80),<br />
.<br />
.<br />
.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks - that was the problem (I knew it would be something obvious  )
I actually had int = 4 in other SProcs so I've changed those values to just int now in case anyone else is interested and they all work fine.
|
|
|
|
|
Mmm. Blue boy is right, your equals to syntax is just assigning default values to the parameters. Your varchar is being declared with the default length of 1.
|
|
|
|
|
try this ok?
sql_cmd.Parameters.Add("@country", SqlDbType.varchar).Value = textbox_GrandPrixCountry.text;
what do you get from this???
Member 3402886 wrote: objCmd.Parameters.Add("@Reg", "Test Reg");
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi Everyone,
I have created one dtsx package in Sql Server Integration Services. Is there any way to assign revision number to my package? How can I update that revision number. 
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
If you've created the package using Business Intelligence Studio, hit F4 on the package and you'll find version number, major and minor.
|
|
|
|
|
Hi i want to update the "name" element in XML using the OPENXML in SQLServer. below is what i'm trying to do without success. Im not even sure if its possible. The value "xxxxx" is normally the name of a table, but when dealing with xml i'm not sure what should be there. Im passing in the xml as a parameter to the stored procedure and doing suff to it. but the last thing in the stored procedure is to update the "name" element. not sure how to do this need some help please. Thanks ONeil
update xxxxxxx
set OPENXML (@hDoc, '/Employees/Employee')
WITH (tName varchar(20) './Name') = 'UpdatedName'
|
|
|
|
|
Have a look at XML DML and especially updating a value: replace value of (XML DML)[^]
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Thanks that helped alot. I manage to get cross that hurdle but now im faced with another problem. im getting error --- "The argument 1 of the xml data type method "modify" must be a string literal" --- for the below statement. I want to take the parameter @code and make it part of the insert statement. if i hardcode a value in the insert statement it work but once i reference the "@code" im getting "The argument 1 of the xml data type method "modify" must be a string literal". Any help would be appreciated
########## XML passing IN ###############
<Employees>
<Employee ID= '111'><Code>1</Code><Name>Name1</Name></Employee>
</Employees>
############# Stored Procedure ###########
ALTER PROC [dbo].[sp_Testing]
@empdata xml,
@code varchar(255)
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
Select @empdata
set @empdata.modify('insert <Employee ID= ''101''>
'+@code+'</Code><Name>''Name101''</Name></Employee>into (/Employees)[1]')<br />
<br />
--return XML<br />
Select @empdata
|
|
|
|
|
Would this piece code perform the operation correctly? If I understood correctly, you want to insert an element to the xml data so you don't need to dynamically parse or concatenate the string.
declare @empdata xml
set @empdata = '<Employees><Employee ID= ''111''><Code>1</Code><Name>Name1</Name></Employee></Employees>'
Select @empdata
set @empdata.modify('insert <Employee ID= ''101''></Employee> into (/Employees)[1]')
Select @empdata
The result:
<Employees>
<Employee ID="111">
<Code>1</Code>
<Name>Name1</Name>
</Employee>
<Employee ID="101" />
</Employees>
Mika
The need to optimize rises from a bad design
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
