Convert Varchar into Time in SQL Server

Question

How do i convert the Time format " 10:02:22 PM " into an SQL Server DateTime Format.

I have a column of such data. I imported this column from a CSV File and now want to convert it into a DateTime Format so that i can be able to use Date Time Functions.

I want to be able to insert this column into another table with correct DateTime format.

Solution 1

Use this link : http://msdn.microsoft.com/en-us/library/ms187928.aspx.

For only time conversion...

SELECT CONVERT( TIME, '10:00:22 PM' );

Gives the following output...

22:00:22.000000

Time conversion with date and time...

SELECT CONVERT( DATETIME, '10:00:22 PM' );

Gives the following output...

1900-01-01 22:00:22.0000 

Note : For datetime you need to have specific date as input otherwise it consider default century date.

Solution 2

You don't need to convert it. An implicit cast occurs when you use

INSERT otherTable
SELECT ....., timeAsVarchar, ...
  FROM csvTable

If all the time data (leading space or not) is parseable from the string, the query will work beautifully. If however, there is the possibility of bad or blank data that cannot be converted to a time, test it first

INSERT otherTable
SELECT ....., CASE WHEN ISDATE(timeAsVarchar)=1 THEN timeAsVarchar END, ...
  FROM csvTable

ELSE NULL is implied so I left it out.