combine date and time column problem

Using SQL Server 2005

Date Time

20060701 090000
20060702 020000
20060703 180000
...

Date and Time datatype is varchar

Tried Query

select Convert(datetime, Convert(char(10), date, 103) + ' ' + Convert(char(8), time, 108), 103) from table
SELECT
CAST(
        DATEADD(dd, 0, DATEDIFF(dd, 0, dat开发者_如何学Ce)) + ' ' +
        DATEADD(Day, -DATEDIFF(Day, 0, time), time)                   
as datetime) from table

It showing error as out of range value.

How to solve this issue.

Need Sql Query Help

First off, why are you storing a DATETIME in a VARCHAR?

This should be able to help

DECLARE @Table TABLE(
        Val VARCHAR(20)
)

INSERT INTO @Table (Val) SELECT '20060701 090102'
INSERT INTO @Table (Val) SELECT '20060702 020000'
INSERT INTO @Table (Val) SELECT '20060703 180000'

SELECT  *,
        CAST(SUBSTRING(Val,1,8) + ' ' + SUBSTRING(Val,10,2) + ':' + SUBSTRING(Val,12,2) + ':' + SUBSTRING(Val,14,2) AS DATETIME)

FROM    @Table

I came across a similar issue a few years ago, when importing HL7 messages. Here is a copy of the function I used. It creates a DateTime string with the time component correctly separated into hh:mm:ss, which is needed for the cast to DateTime.

CREATE FUNCTION fn_StringDateTietoDateTime
(
    @Date varchar(15)
)
RETURNS datetime
AS
BEGIN
    DECLARE @Result DATETIME

    SET @Result = NULL

    If len(@Date) > 0
    BEGIN
        SELECT @Result = CAST(SUBSTRING(@hl7date, 1, 8) + ' ' + SUBSTRING(@hl7date, 10, 2) + ':' +
            SUBSTRING(@date, 12, 2) + ':' + SUBSTRING(@date,14, 2) AS DATETIME)
    END

    RETURN @RESULT
END