Database

DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType>
     MED  
</PolicyType>
<PolicyType>
     SPORT
</PolicyType>
</NewDataSet>
','utf-16','UTF-8');

DECLARE @PolicyTypes TABLE 
( 
    PolicyType char(5) 
);

INSERT INTO @PolicyTypes
(
    PolicyType
)
SELECT
    LTrim(Replace(Replace(Replace(
        T.PolicyType.value('.', 'varchar(120)')
    , Char(13), ''), Char(10), ''), Char(9), ''))
FROM
    @xmlPolicyType.nodes('/NewDataSet/PolicyType') As T (PolicyType)
;

Richard MacCutchan wrote:

What am I doing wrong?

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER, FOREIGN KEY (ClassId) REFERENCES Classes(ClassId) )

Richard MacCutchan wrote:

your suggestion did work (for SQL CE).

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

LTM_it_kh wrote:

But this may not the best choice for me.

SELECT DATEDIFF(hour, START_DATE,END_DATE) FROM YOURTABLE

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

SELECT [dbo].[ufn_GetTimeDifference]('32:10', '26:10') AS 'Time Difference';

SELECT [dbo].[ufn_GetTimeDifference]('72:00', '36:33') AS 'Time Difference';

SELECT [dbo].[ufn_GetTimeDifference]('25:25', '25:25') AS 'Time Difference';

SELECT [dbo].[ufn_GetTimeDifference]('26:10', '32:10') AS 'Time Difference';

Andrius Leonavicius wrote:

I'm considering the usage of VARCHARs as given inputs (which cannot be changed for some reason)

Robymon wrote:

32:10 - 26:10