|
Start by looking at the DATEDIFF function.
|
|
|
|
|
Here you go.. pretty darn simples.
declare @dt1 as datetime
set @dt1 = '2008-12-12 13:46:26'
declare @dt2 as datetime
set @dt2 = '2008-12-12 23:48:06'
declare @dt3 datetime
set @dt3 = @dt2-@dt1
select CONVERT(VARCHAR(10),@dt3,108)
108 is the format for HH:mi:ss
|
|
|
|
|
Mr. J4amieC solution is perfect which will work everywhere.
However, if you are using Sql Server 2008, you can use the new TIME datatype something like this
declare @tbl table(startdate datetime,enddate datetime)
insert into @tbl select '2008-12-12 13:46:26','2008-12-12 23:48:06'
select CAST((select enddate-startdate from @tbl) as time) TimeDiff
Niladri Biswas
modified on Wednesday, October 28, 2009 5:56 AM
|
|
|
|
|
Let me get this straight, you are casting a DATETIME to a DATETIME so you can cast it to TIME?
only two letters away from being an asset
|
|
|
|
|
Yae.. I didnot notice that I made that mistake.
Very sorry. But thanks.
Niladri Biswas
|
|
|
|
|
I don't know if this is the right form to ask this but I didn't know where else to post it.
I have windows server 2003 and in it I have SQL server 2000. My question is can I install SQL 2005 over my server and have both 2000&2005? Does this make any problem? Can I have both SQlServers over the same windows server?
Thanks in Advance.
|
|
|
|
|
You will not be installing 2005 OVER 2000, that is an upgrade, you should install 2005 using a named instance on the server. Then you use the named instance in the connection string in your apps.
|
|
|
|
|
So if i do this i can have both sql servers 2000%2005 on my machine?
|
|
|
|
|
You should be able to, have you Googled to see if you can?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, and 2008 as well if you want.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
crystal reports r new to me i want to make a query using Add Command and want to include a condition Between for a Date. How i can do that? when i done it i create parameters also usin CREATE PARAMETER but when i give value to Date1 and click ok it show error Permission violation, or other nonsecific error and give another error failed to open a rowset. here is my query plz chk and reply. thnx in advance
SELECT Sum(b.TotalSale*b.Price) AS total, a.Category , a.[Transaction]
FROM TransactionType AS a INNER JOIN CouponTransaction AS b ON a.TransactionTypeID = b.TransactionTypeID
where b.Transactiondate Between ({?Date1},{Date2})
GROUP BY a.Category,a.[Transaction];
|
|
|
|
|
I suggest you take your select processing away from CR and move it back to your database where it belongs. When I worked with CR , years ago, we would build a stored proc (in SQL Server) that returned the exact set of data to service the report. Testing the data set is dramatically quicker in SQL management studio. Then use a parameterised query to get the data from the proc.
|
|
|
|
|
thnx 4 the suggestions but i want to get Date parameters value at run time.what should i do?
|
|
|
|
|
i want to pass an sql query to generate crystal reports
is it possible.if so please guide me
|
|
|
|
|
One way is to bind your report to a dataset. Here's[^] a tutorial.
|
|
|
|
|
I have capture screen where a user will capture daily production figures for several performance categories, i.e. they will enter a single figure per category on any particular day.
Now I don't want the user to insert a new record for each category-figure record, so I plan to check if the day has records for all categories. If not, before opening the capture screen, I will add a record for each category to that day.
One problem is that over time, categories can be added, so if I have 30 categories, and check if Oct 12 has 30 categories, it could be that on Oct 12 there were only 27 categories. I will then need a CreatedDate on the categories, and when I check categories for a particular day, I will only count categories created on or before that particular date.
Any other means of capturing daily figures besides my capture screen will be prohibited, so I am fairly confident that if only a sub-set of categories exists for any given day, they were not properly created, and I can delete them and recreate all categories for that day.
EDIT: I can no longer do the above, as the user may capture data for one day, and in the same day decide to add a category. Using only categories with a create date earlier than the current date will prevent the new category being visible today. Not using the 'earlier date' method will result in today's data being cleared.
Is this design sound? Can I do anything to improve it?
|
|
|
|
|
This is obviously too hard to answer - probably belongs in the design forum anyway....
This seems reasonable and as for the edit, what not have a >= today?
How do you intend to support a category that needs to be retired? I love asking these questions, years of trying to answer them I guess.
Have a from to date where the default to date = datetime.maxvalue (31/12/9999) and use where today between from and to
Caveat - datetimepicker does not support datetime.maxvalue.
|
|
|
|
|
hi i am new to sql soplz give me idea about it
how to find missing numbers which is given in table using sql query,
e.g.
i/p
1
2
3
4
5
6.....
for query
1
2
5
8
o/p????????
3
4
6
7
how it coes by query plz tellme any body
|
|
|
|
|
Try this
declare @Table1 table(id int)
insert into @Table1
select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8
declare @Table2 table(id int)
insert into @Table2
select 1 union all select 2 union all select 5 union all select 8
Solution 1:
select id from @Table1 except select id from @Table2
Solution 2:
select id from @Table1 where id not in (select id from @Table2)
Let me know in case of any concern
Niladri Biswas
|
|
|
|
|
How do you link a SQL Server through an ODBC connection to an Access DB that has the User Security turned on? The ODBC connection contains the user name and password. When I test the connection in C# I don't have any problems but my client can't get their SQL Server to work.
This is the error they sent me.
OLE DB provider "MSDASQL" for linked server "testDB" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "testDB" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "testDB" returned message "[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "testDB".
|
|
|
|
|
Hi, I have a problem and I'm not even sure what question to ask..
I suspect my problem lays in the design, I have these three identical sets of table that are filled independently and when combined at some time to have them in one table we will have the primary key (ID is a primary key in all the table copies) violated (where set to bold)
Please, how would you do it D'Oh!
ID IDD Name
-- --- ----
1 1 A
2 1 B
3 1 C
1 2 A
2 2 B
3 2 C
ID IDD Name
-- --- ----
1 1 A
2 1 B
3 1 C
ID IDD Name
-- --- ----
1 2 A
2 2 B
3 2 C
modified on Friday, October 23, 2009 4:53 PM
|
|
|
|
|
Perhaps it's just my monitor but the bold you mention is difficult to see. If I understand your problem however;
First, you could ignore the primary keys and insert the other columns into you new table. Provided there is no associations to them that must be maintained
Second, create the combined table as such NewId(PK), ID, IDD, Name
only two letters away from being an asset
|
|
|
|
|
Thank you Mark, I had the very same thought, but then I'll have to make the NewID primary key column autonumbered "I've done this before".. please notice that these tables are not no the same platform and cant be related as I would export the lower copy and combine it with the upper one "the one you suggested to have a new ID primary column".. Anyhow, thanks mate and I'll just wait for other replays today and if no better solution was suggested, I'll go for this one.
|
|
|
|
|
Hey Mark, just to let you know, I found the answer and it was to simply create a composite key!
|
|
|
|
|
Hi,
I use connection to sql server:
Internal::_ConnectionPtr m_pConnection;
m_pConnection.CreateInstance( __uuidof( Internal::Connection ) );
when I am currently testing my application in remote (server sqlserver in another machine) everything works as it should.
But when I simulate a network outage (disconnect and reconnect the cable network) and I run a query to read attributes (for example) I noticed that my application does not know the basis which has been connected even though the tests on the connection it gives me yet opened(pConnection->GetState() = adStateOpen)!
So I wonder how to know if there is a network outage or how to create a connection attempt and also the best test on the connection that it is still connected to my database.
Thank you
|
|
|
|