|
Dear Mycroft Holmes,
Thanks you so much for your solutions, I will test it with global temp table...
But this may not the best choice for me..
Regard
Mr.LTM-KH
|
|
|
|
|
LTM_it_kh wrote: But this may not the best choice for me.
It may well be the worst solution! Global temp table are nasty to manage!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to calculate Total Time difference like this
eg: 32:10 - 26:10 = 06:00
How to do this with the sql query?
|
|
|
|
|
What database are you using?
Is 32:10 minutes:seconds?
What have you tried?
|
|
|
|
|
32:10 means
32 Hours and 10 Minutes, I want to find this Total Hours Difference in Hour and Minutes
|
|
|
|
|
What you might want to do is convert the hours to minutes then subtract then convert back.
|
|
|
|
|
In MS-SQL you can try
SELECT DATEDIFF(hour, START_DATE,END_DATE) FROM YOURTABLE
|
|
|
|
|
Hi,
----------
Note: I'm making an assumption that time values are given inputs as VARCHARs (data type cannot be changed for some reason). Also, please take a look at Mycroft's comments and answer below.
----------
You can create a user-defined function for this purpose. Here is an example of such a function for SQL Server:
CREATE FUNCTION [dbo].[ufn_GetTimeDifference] (@SecondTime VARCHAR(20),
@FirstTime VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @TotalMinutes INT,
@IsNegative BIT = 0,
@Hours INT,
@Minutes INT,
@TimeDifference VARCHAR(20);
SET @TotalMinutes = ( ( LEFT(@SecondTime, ( CHARINDEX(':', @SecondTime) - 1 )) * 60 ) + RIGHT(@SecondTime, 2) ) -
( ( LEFT(@FirstTime, ( CHARINDEX(':', @FirstTime) - 1 )) * 60 ) + RIGHT(@FirstTime, 2) );
IF ( @TotalMinutes < 0 )
BEGIN
SET @IsNegative = 1;
SET @TotalMinutes = ABS(@TotalMinutes);
END
SET @Hours = ( @TotalMinutes / 60 );
SET @Minutes = ( @TotalMinutes % 60 );
SET @TimeDifference = ( CASE
WHEN ( @IsNegative = 1 ) THEN '-'
ELSE ''
END ) +
( CASE
WHEN @Hours < 10 THEN '0' + CAST(@Hours AS VARCHAR(1))
ELSE CAST(@Hours AS VARCHAR(20))
END ) +
':' +
( CASE
WHEN @Minutes < 10 THEN '0' + CAST(@Minutes AS VARCHAR(1))
ELSE CAST(@Minutes AS VARCHAR(2))
END );
RETURN @TimeDifference;
END
GO
As you can see, second time and first time parameters are varchars, which expects time in the HH:MM format. The function returns positive or negative time difference as varchar.
Examples of usage:
SELECT [dbo].[ufn_GetTimeDifference]('32:10', '26:10') AS 'Time Difference';
06:00
SELECT [dbo].[ufn_GetTimeDifference]('72:00', '36:33') AS 'Time Difference';
35:27
SELECT [dbo].[ufn_GetTimeDifference]('25:25', '25:25') AS 'Time Difference';
00:00
SELECT [dbo].[ufn_GetTimeDifference]('26:10', '32:10') AS 'Time Difference';
-06:00
Regards,
Andrius Leonavicius
modified 30-Jul-14 17:45pm.
|
|
|
|
|
This is a bad answer, recommending that someone uses varchar for date function is the WORST thing you can do, globalisation (and crappy data) screws them every time. You should only accept a datetime format and use the databases datetime functionality to deal with it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Thanks for the reply.
First of all, I'm not recommending that someone should use VARCHAR for storing time (date) values. I agree on this part with you.
However, I'm looking at this problem from a different perspective than you do. I'm considering the usage of VARCHARs as given inputs (data type cannot be changed for some reason). Let's take a look at OP's example once more: "eg: 32:10 - 26:10 = 06:00". So 32:10 and 26:10 are VARCHARs and they cannot be converted to DATETIME ...
I made a really bad assumption that OP is aware of DATETIME usage in such situations (as you said) and haven't warned about that.
Regards,
Andrius Leonavicius
modified 29-Jul-14 20:25pm.
|
|
|
|
|
Andrius Leonavicius wrote: I'm considering the usage of VARCHARs as given inputs (which cannot be changed for some reason)
That is a premise I would not accept, if it gets to the database then it must be in a valid format, the OP should be going back to the input and cleaning up the data before it gets into the database. He has more fundamental problems than getting the difference here.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well, you're right. +5 for your answer.
Regards,
Andrius Leonavicius
|
|
|
|
|
Robymon wrote: 32:10 - 26:10
As has been pointed out by others this is NOT a valid data format, either change the format to 2 ints (hours and minutes) or convert them to minutes. You can then work on the data using math or datetime functions.
While Andrius solution is valid for chopping up strings you should not be dealing with strings in the first place. Fix your underlying data problems and this issue disappears.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<pre lang="SQL">
SELECT [Id],
CONCAT (DATEDIFF(HOUR ,StartDate, EndDate),
N' : ',
(DATEDIFF(MINUTE,StartDate, EndDate) % 60)) as TimeTaken
FROM [dbo].[YourTable]
Above example, I am first finding the Hour, then finding the minutes. Finally, do a Concatenation to get the results in the format of HH:MM. Hopes this helps.
|
|
|
|
|
i completed my education in 2010... i did my course on sql server 2005 dba.. can any tell me were can i find pdf of dba and bi so that i can get a proper grip of it and if possible can anyone share with me a live project that you have performed .. it will be very useful for me to crack the interview .. i wanna attend the interview genuinely..
kindly help me out.... thanks
ganesh...
|
|
|
|
|
If you completed your education 4 years ago on SS 2005, you should probably download an Express version that is a little more current. It may not allow you do everything, but you should be able to do a lot with it.
And to learn, practice. Create a database, implement it. Test it.
Show what you've done before asking for help.
|
|
|
|
|
There are 2 distinct streams when dealing with databases, developer and DBA. CP mostly deals with developer requirements, how to move data around an application and process the data.
If you intend to go down the DBA path, where you maintain the health of the database and development is only incidental to your core role then http://www.sqlservercentral.com/[^] is a good resource. You may also want/need to concentrate on a specific database server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello everyone,
I have a problem with my SSIS 2012, if I put in made a OLDBE Source with a complex query or composite sample
AS WITH TT
(
SELECT DISTINCT
e.UniqueID e. [Web Id] from Elements e) SELECT
DISTINCT *
'Class' + CAST (ROW_NUMBER () OVER (PARTITION BY [Web Id] ORDER BY [class] DESC) AS NVARCHAR) AS ClassNum
from TT
I have this error:
Exception From HRESULT: 0xC0202009
Error at Get Products SSIS Error Code DTS_E_OLEBDERROR. A OLE DB error has occured. Error code: 0x80004005
An OLE DB record is available. Source "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Syntax Error, permission violation, or nonspecific Reviews another error"
see Exhibit attached
Note that this same package works wonders on my colleague's machine with vs 2010
I do not understand the error!!
modified 24-Jul-14 8:04am.
|
|
|
|
|
This is an English-language forum. Please post your questions in English.
Il s'agit d'un forum de langue anglaise. S'il vous plaît envoyer vos questions en anglais.
https://translate.google.com/[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
Is your package running as a SQL Agent job? You might need to change the permissions on the TEMP directory:
http://support.microsoft.com/kb/933835[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i run it from VS2012 so i dont know if it s with sql agent or not (beginner )
and where i can find Temp directory
|
|
|
|
|
If you're running it from Visual Studio, then it's not using SQL Agent, so that's probably not the problem.
There seem to be lots of potential causes for that error message:
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
all of this properties are Ok and as i said this same package works wonders on my colleague's machine with vs 2010
i dont think it s a problem with connection string or else
|
|
|
|
|
amioni wrote: e.UniqueID e. [Web Id] from Elements I am not seeing a comma between the two columns you reference, is that correct?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|