Well, you're right. +5 for your answer.

Regards, Andrius Leonavicius

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

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](

I'd recommend to turn off the Auto Close:

ALTER DATABASE database_name
SET AUTO_CLOSE OFF;
GO

Speaking about errors, try to restart the server and run DBCC CHECKDB again. Also, take a look at Windows Event Logs for errors. BTW, do you have enough disk space?

Regards, Andrius Leonavicius

Hi, Usually it's because there are corruption problems related to the storage system. Have you tried running DBCC CHECKDB against the database?

DBCC CHECKDB ('Your DB Name') WITH NO_INFOMSGS;
GO

This could give you more information. Also, check the Auto Close property:

SELECT DATABASEPROPERTY ('Your DB Name', 'IsAutoClose');
GO

If it returns 1 (Auto Close is on), then that may be the reason of your problem...

Regards, Andrius Leonavicius

You're welcome. :)

Best regards, Andrius Leonavicius

Hi, The problem is not a ROUND function, but the precision of the FLOAT data type (Using decimal, float, and real Data[^]). You have at least a couple of options: 1. Use DECIMAL or NUMERIC instead of the FLOAT. 2. CAST to DECIMAL for the calculation. Here's the demonstration for you (using your first example): 1. Select using ROUND.

SELECT ROUND((7.5169 * 745), 3) AS Result;

or

SELECT ROUND(5600.0905, 3) AS Result;

Result: 5600.0910 2. Using FLOAT variables.

DECLARE @a FLOAT, @b FLOAT, @c FLOAT;
SET @a = 7.5169;
SET @b = 745;
SET @c = @a * @b;
SELECT ROUND(@c, 3) AS Result;

Result: 5600.09 3. Using DECIMAL variables.

DECLARE @a DECIMAL(18, 4), @b DECIMAL(18, 4), @c DECIMAL(18, 4);
SET @a = 7.5169;
SET @b = 745;
SET @c = @a * @b;
SELECT ROUND(@c, 3) AS Result;

Result: 5600.0910 P.S. I am posting such data with the XML pre tag. Also, I am inserting (copying) TAB characters (if needed).

Best regards, Andrius Leonavicius

Hi, After clicking "Modify the comment.", I can see   and <br> tags in the comment. After submitting the edited comment, <br> tags remain in the comment...

Best regards, Andrius Leonavicius

Hi, I don't understand what exactly you want to do, but there are two ways to insert data: 1. Insert values.

INSERT INTO Table1 (Column1, Column2)
VALUES (@Value1, @Value2);

2. Insert from table/tables.

INSERT INTO Table1 (Column1, Column2)
SELECT Column1, Column2 FROM Table2
WHERE Column1 = @Parameter;

or something like

INSERT INTO Table1 (Column1, Column2)
SELECT t2.Column1, t3.Column2 FROM Table2 AS t2
INNER JOIN Table3 AS t3 ON t2.ColumnID = t3.ColumnID
WHERE t2.Column1 = @Parameter;

Best regards, Andrius Leonavicius

32°C? :omg: Current weather in Vilnius, Lithuania: 7°C and sprinkles (08:57 AM)... :|

You can't downgrade using a backup file. The only way is to use scripting: How To Convert SQL Server 2008 Database To SQL Server 2005?[^] or some comparison tools like Red Gates's SQL Compare and SQL Data Compare: SQL Compare 10[^] SQL Data Compare 10[^].

Wow, I am speechless... :omg: :thumbsup:

This is a good one. :laugh: :thumbsup:

Got it. :)

This is interesting. :) Google Search exaggerated this. :laugh: At first, it says "About 30,200 results", but if you press page 10, then you are going to see this: "Page 10 of 91 results" (at this moment, it might be different for you). So it's 91, nothing close to 30,200... :wtf:

Vistaberculosis :laugh:

I hope that Microsoft will make 'Office Online' better than Office Web Apps...

Hi, I'm not sure what are you trying to do... Maybe you could give some example?

Yes, it is. :laugh: But it would be more logical if we wouldn't be able to power off the machine, which is already powered off. :)

Just because I don’t care doesn’t mean I don’t understand – Homer Simpson :)