combine date and time column problem

Question

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, date)) + ' ' +
        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

Answer 1

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

Answer 2

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