About Me

My photo
MCTS FOR SQL SERVER 2008 DOT NET PROGRAMMING WEB DESIGNING

Monday, 17 February 2014

Convert Text Data to DATETIME using STUFF method

--create a table emulating a fixed-width text import
DECLARE @Imports TABLE (
 DATEFIELD char(19) NOT NULL,
 DATAFIELD char(10) NOT NULL )
INSERT INTO @Imports VALUES
('2010:09:09 16:00:46','$    10.88'),
('2010:09:16 13:56:41','$    21.68'),
('2010:09:16 14:05:50','$     5.57'),
('2010:09:19 16:43:31','$    15.33'),
('2010:09:19 18:42:21','$   259.23'),
('2010:09:25 13:50:29','$    73.79'),
('2010:09:25 13:50:37','$    46.92')
 
SELECT DATEFIELD,
 CONVERT(DATETIME, STUFF(STUFF(DATEFIELD, 8,1,''),5,1,'')) AS [DATEFIELD Converted],
FROM @Imports

No comments:

Post a Comment