CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2005
Vikas Kunte
Vikas Kunte

Reputation: 731

combine 2 varchar column's data and convert to datetime

I have 2 columns in a table of varchar datatype.

date and type are the column names in table.

the data present in the table looks like this

date        time
20090610    132713
20090610    132734

i need ms sql server query to concatenate these 2 columns data and display as datetime format.

Note : 1. the datatype of those 2 columns cannot be changed now. 2. i tried 

select convert(datetime,date + time)

it says "Conversion failed when converting date and/or time from character string."

Suggest the possible solution.

Upvotes: 1

Views: 2935

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44316

This will return a datetime. The bottom line is to be replaced by your table

select convert(datetime,date,112)+
coalesce(stuff(stuff(rtrim(time), 5,0,':'), 3,0,':'), '') newdate
from 
(VALUES ('20090610','132713'),('20090610', '132734'),('20090610', '      ')) yourtable(date,time)

Result:

newdate
2009-06-10 13:27:13.000
2009-06-10 13:27:34.000
2009-06-10 00:00:00.000

Upvotes: 2

Myles J
Myles J

Reputation: 2880

CREATE FUNCTION [dbo].[DateTimeAdd] 
( 
      @datepart         date, 
      @timepart         time 
) 
RETURNS datetime2 
AS 
BEGIN 
      RETURN DATEADD(dd, DATEDIFF(dd, 0, @datepart), CAST(@timepart AS datetime2)); 
END

Sorry - Missed the bit in your question about storing the date and time as varchars. You would therefore still need to convert these data itemsbefore using this function.

Upvotes: 0

whytheq
whytheq

Reputation: 35557

CREATE TABLE #Table 
(
[date] VARCHAR(100),
[time] VARCHAR(100)
)
INSERT INTO #Table VALUES
('20090610','132713'),
('20090610','132734')   

;WITH Bits_CTE
    AS
    (
    SELECT 
        [Date],
        [Time],
        [hrs] = CONVERT(INT,SUBSTRING([Time], 1, 2)),
        [mns] = CONVERT(INT,SUBSTRING([Time], 3, 2)),
        [secs] = CONVERT(INT,SUBSTRING([Time], 5, 2))
    FROM #Table
    )
SELECT 
    [Date],
    [Time],
    DATEADD(HOUR,[hrs],
    DATEADD(MINUTE,[mns],
    DATEADD(SECOND,[secs],[Date]))) 
FROM Bits_CTE

Upvotes: 0

Shaikh Farooque
Shaikh Farooque

Reputation: 2630

You can get it using 

SELECT 
convert(varchar, convert(datetime, date), 111)
+ ' ' + substring(time, 1, 2)
+ ':' + substring(time, 3, 2)
+ ':' + substring(time, 5, 2)

Upvotes: 0

Related Questions