|
I've been asking around about this specific problem I have comparing times,
but as you know dates are stored in the following format: 2005/07/21 03:18:10 PM.
I want to select all between 08:10:00 and 08:55:00, disregarding the actual date. This will
then tell me when the labs are the busiest during the day.
this is the query:
select count (*) from User_Lab_Access
where LabID = 'R229'
and DateTime >= '2005/07/21 08:10:00'
and DateTime <= '2005/07/21 08:55:00'
* Remember disregarding the yyyy/mm/dd
|
|
|
|
|
I do not know if it is the best solution, but this should work:
select count (*) from User_Lab_Access<br />
where LabID = 'R229'<br />
and DateTime -floor(cast(DateTime as float)) between '08:10:00' and '08:55:00'<br />
Wout Louwers
|
|
|
|
|
Try:
select count (*) from User_Lab_Access
where LabID = 'R229'
and Convert(char(8), DateTime, 8) between '08:10:00' and '08:55:00'
|
|
|
|
|
This will work in SQL server converting the time funcation into an Int between 0 and 2359
select count (*) from User_Lab_Access
where LabID = 'R229'
AND ( (DATEPART(hour, DateTime) * 100) + (DATEPART(minute, DateTime)) ) > 810 )
AND ( (DATEPART(hour, DateTime) * 100) + (DATEPART(minute, DateTime)) ) < 855 )
|
|
|
|
|
Hi!
I want to Add/Update/Get one record at one time.
suppose i have userTable with
int userID
nvarchar username
nvarchar email
to add data to userTable which is better?
1. using DataRow and fill it with userID,username,email and pass it to DataAccessLayer
2.Create user Class (USEREN) and using USEREN userBObj and fill it with userID,username,email and pass it to DataAccessLayerDataAccessLayer
thanks.
|
|
|
|
|
Personally, I would just create a stored procedure, create an SqlCommand with the relevant parameters and Execute it.
SqlCommand cmd = new SqlCommand("AddUser", myConnection);
cmd.Parameters.Add("@userID", theUserID);
cmd.Parameters.Add("@userName", theUserName);
cmd.Parameters.Add("@email", theEmailAddress);
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery();
And the stored procedure would look like this:
CREATE PROCEDURE dbo.AddUser
@userID int,
@userName nvarchar(50),
@email nvarchar(50)
AS
INSERT userTable(userID, username, email)
VALUES (@userID, @userName, @email);
GO
The above C# code would go in the DAL (Data Abstraction Layer).
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks
But i want to find best way to Pass userID , username , email between Layers of a n-tier System.
|
|
|
|
|
|
|
Hi all,
Corresponding .cs file is not added when I add a Crytal Report file in Solution Explorer VS.Net2003. And therefore I am unable to use Strong-Typed report object in my C# code.
I have added the References
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Windows.Forms;
and I selected 'Show All Files' in Solution Explorer.
There is no error in compiling the project. So why the corresponding .cs file of the .rpt file is not added in my Solution Explorer?
Please help.
Arif.
|
|
|
|
|
hi friends,
any one please help me how to store a word document into sql server's blob.
|
|
|
|
|
I have to compare data in a table in a SQL Server DB at two different points of time to check if the concerned rows have been modified/deleted/new additions.
I am using a Stored Procedure to retrieve data the DB and save the data in another table. Now at a later time, I am retrieving this saved data into a first dataset. At the same time, I am using the Stored Procedure I used earlier and getting the current data in the second dataset. Now I have to compare these two datasets and get the modified/deleted/new rows. Any suggestions of how to do this?
All your help is appreciated.
Cheers
Kathy
|
|
|
|
|
Have you tried using Diffgrams
|
|
|
|
|
I am having a problem while working with DataAdapter and linked server. What is happening is that When I select records using "select * from bk...tablename" it gets the record in dataset but when I want to update that dataset it omits the "..." link "bk.tablename" so it does not find the Linked Server.
Any suggestion?
" I have found a new world but... lost the one I belong to... "
|
|
|
|
|
update linkedserver...tablename
SET myfield = 'Hello'
When mykey = 1
|
|
|
|
|
Thanks for the reply but my problem is that I want to do it with the help of Data Adapter  Thanks any ways
" I have found a new world but... lost the one I belong to... "
|
|
|
|
|
hi
I m passing the date parameter to a stored procedure. The stored procedure not does not accept the date format as 'dd/mm/yyyy'. It gives an error -"error converting data type varchar to datetime".
My machine's regional setting is set to 'UK' i.e. "dd/mm/yyyy".
How can i make a change in SQL server so that it accepts date parameters in the "dd/mm/yyyy".
please help
shelly
|
|
|
|
|
|
1. Don't concatenate parameters in your command string - use a SqlParameter object (ADO.NET) or the Parameters collection of the Command object (ADO).
2. If you must use a literal date, use the ISO format 'yyyymmdd'. SQL Server will always accept this format, regardless of locale settings.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I never pass any dates to a SQL server I always pass DD MM YY then build the date on the fly in the SQL server Strored Proc. Example below.
###########################################################################################
CREATE PROCEDURE usp_myproc
@YearInt int,
@MonthInt int,
@DayInt int
AS
DECLARE @CD_DATE datetime
DECLARE @TempVarChar varchar(50)
Set @TempVarChar = CAST(@DayInt As Varchar(2) ) + '/' + CAST(@MonthInt As Varchar(2) ) + '/' + CAST(@YearInt As Varchar(4) )
Set @CD_DATE = (Convert( datetime, @TempVarChar, 103 ))
SELECT @CD_DATE
go
|
|
|
|
|
Hi Developrs,
I have developed a database in SQL Server 2000. Now I want to generate its ERD which should not contain attributes of Table.
Is it possible with Visio 2002? If it is possible in any way please do reply me its very urgent.
Thanx in advance.
|
|
|
|
|
How can I select one table which exist in another server but actually i am in my server????
|
|
|
|
|
My problem is that I have 1 table that is in the sql server, and another table that is in memory. The table in memory is defined as follows:
Name: outputTable
computer_id int
dracver string
dracip string
user string
password string
serial_num = string
the table in the sql server is defined as follows:
NAME computer
computer_id int
serial_num var
my question is how do I compare the serial_num column in the outputTable to the serial_num column in the computer table using all the rows in the outputTable.
Here is what I have so far
SELECT computer_id, serial_num FROM computer WHERE dbo.computer.[serial_num]="+FailureTable.Columns["serial_num"];
The problem is that it returns everything from the computer table.
can someone PLEASE HELP!!!!
thanxs
connie
|
|
|
|
|
That's a bit strange. It should only return the computer corresponding to the sigle record from the memory table that you use to create the query.
Loop through the memory table to create a string containing a comma separated list of id's. Then you can use that string in a query:
"select computer_id, serial_num FROM computer WHERE serial_num in (" + idList + ")"
---
b { font-weight: normal; }
|
|
|
|
|
The problem with that is that I also need the dracip string from the outputTable that goes with the serial_num being compared.
Any ideas on how I can also get that data as well??
Thanxs
Connie
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
