SQL Server varchar to datetime

Question

I have a field varchar(14) = 20090226115644

I need convert it to -> 2009-02-26 11:56:44 (datetime format)

My idea. use cast and convert.. but I always have errors.

Conversion failed when converting datetime from character string.

I made this, but don`t like it..

SELECT  
    SUBSTRING(move,1,4) + '-' + SUBSTRING(move,5,2) + '-' + SUBSTRING(move,7,2) + ' ' + SUBSTRING(move,9,2) + ':' + SUBSTRING(move,11,2) + ':'+SUBSTRING(move,13,2) as new -- 
FROM [Test].[dbo].[container_events]
where move IS not null

Result :2009-02-26 11:56:44

Answer 1

The CAST operator in SQL Server has a well-defined list of formats which are supported - see on MSDN SQL Server Books Online.

Your format doesn't seem to fit any of those defined formats -> you're on your own, you have to use some custom logic (as you do) to bring that varchar field into a format that CAST can understand.

So what is your question now??

Answer 2

your logic looks correct and works for the given data. however, I'll bet that you have some bad data out there.

try this:

DECLARE @container_events table (PK int, move varchar(14))
SET NOCOUNT ON
INSERT INTO @container_events VALUES (1,'20090226115644')
INSERT INTO @container_events VALUES (2,'20090226116644')
INSERT INTO @container_events VALUES (3,'20090227010203')
INSERT INTO @container_events VALUES (4,'20090228010203')
INSERT INTO @container_events VALUES (5,'20090229010203')
SET NOCOUNT OFF

---list all bad dates
SELECT  
    SUBSTRING(move,1,4) + '-' + SUBSTRING(move,5,2) + '-' + SUBSTRING(move,7,2) + ' ' + SUBSTRING(move,9,2) + ':' + SUBSTRING(move,11,2) + ':'+SUBSTRING(move,13,2) as BadDatesOnly, move
FROM @container_events
where ISDATE(SUBSTRING(move,1,4) + '-' + SUBSTRING(move,5,2) + '-' + SUBSTRING(move,7,2) + ' ' + SUBSTRING(move,9,2) + ':' + SUBSTRING(move,11,2) + ':'+SUBSTRING(move,13,2))=0


---list all bad dates
SELECT  
    CONVERT(datetime,SUBSTRING(move,1,4) + '-' + SUBSTRING(move,5,2) + '-' + SUBSTRING(move,7,2) + ' ' + SUBSTRING(move,9,2) + ':' + SUBSTRING(move,11,2) + ':'+SUBSTRING(move,13,2)) as GoodDatesOnly, move
FROM @container_events
where ISDATE(SUBSTRING(move,1,4) + '-' + SUBSTRING(move,5,2) + '-' + SUBSTRING(move,7,2) + ' ' + SUBSTRING(move,9,2) + ':' + SUBSTRING(move,11,2) + ':'+SUBSTRING(move,13,2))=1

OUTPUT:

BadDatesOnly        move
------------------- --------------
2009-02-26 11:66:44 20090226116644
2009-02-29 01:02:03 20090229010203

(2 row(s) affected)

GoodDatesOnly           move
----------------------- --------------
2009-02-26 11:56:44.000 20090226115644
2009-02-27 01:02:03.000 20090227010203
2009-02-28 01:02:03.000 20090228010203

(3 row(s) affected)

Using the ISDATE (Transact-SQL) function you can determine if the date is valid or not. AS a result you can filter out the bad rows in your query, or find the bad rows and fix them, etc. it is up to you what to do with the bad data.