Convert Varchar during SQL Server

Question

How to convert the "10:02:22 PM" time format to SQL Server date format.

I have a column of such data. I imported this column from a CSV file and now I want to convert it to DateTime format so that I can use Date Time functions.

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

Answer 1

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

Only for time conversion ...

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

Gives the following result ...

 22:00:22.000000 

Converting time with date and time ...

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

Gives the following result ...

 1900-01-01 22:00:22.0000 

Note. For datetime you need to specify a specific date as input, otherwise it will be considered by default.

Answer 2

You do not need to convert it. Implicit listing occurs when you use

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

If all the time data (master space or not) is processed from a string, the query will work beautifully. If, however, it is likely that bad or empty data cannot be converted in time, first check it

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

ELSE NULL implied, so I left it.