Convert Varchar Column to Datetime format - SQL Server

Question

I have a table of data imported from CSV as follows:

FirstTimeTaken      LatestTimeTaken     Market                  Outcome         Odds    NumberOfBets    VolumeMatched   InPlay
03/08/2013 15:30:14 03/08/2013 15:32:28 Over/Under 3.5 Goals    Over 3.5 Goals  5       10              118             1
03/08/2013 14:26:40 03/08/2013 14:29:43 Correct Score           0 - 0           7       12              279             1
03/08/2013 15:15:34 03/08/2013 15:27:39 Match Odds              Barnsley        110     7               9               1
28/07/2013 16:57:26 29/07/2013 21:35:55 Match Odds              Barnsley        3       9               35              0

I had to import the first 2 columns in varchar format because I was getting errors trying to import as datetime. Now I have the data in a table, I need to convert the Column format from Varchar to Datetime. I tried:

ALTER TABLE #CSVTest_Data
ALTER COLUMN FirstTimeTaken DATETIME
ALTER TABLE #CSVTest_Data
ALTER COLUMN LatestTimeTaken DATETIME

This results in error: 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value'. I know that removing the last row of data gets rid of the problem, so I suspect that the system thinks the date format is MM/DD/YYYY whereas it is actually DD/MM/YYYY. The following query works fine:

SELECT convert(VARCHAR(50),FirstTimeTaken,105) from #CSVTest_Data 
SELECT convert(VARCHAR(50),LatestTimeTaken,105) from #CSVTest_Data

But this does not convert the column format to datetime. I would appreciate some help on how to do this. Thanks.

Answer 1

Try using SET DATEFORMAT.

SET DATEFORMAT dmy

Answer 2

You can select the data from your #Temp table as follows:

SELECT 
    CONVERT(DATETIME, FirstTimeTaken, 103 ),
    CONVERT(DATETIME, LatestTimeTaken, 103)
FROM #CSVTest_Data

This returns the fields as DATETIME data types. From there, do whatever you need to.