Sql Convert Text Field To Date Field

I'm importing an access database to sql.

The original access database has a date field that imports nicely, but the time field is text (10:00 AM, for instance).

I have over 4000 records and assume there is a way to convert 10:00 AM to 10:00:00.000 (or 07:30 PM to 19:30:00.000, etc...) so that it can be combined with the pre-existing dat开发者_运维问答e field (which is now like 2011-11-11 00:00:00.000).

Also, if it's easy to do the conversion and concatenation in the same process, please note.

to convert the time from am or pm 10:00 PM format into time format 10:00:00.000:

select cast(timestring as time(7))

look this:

declare @timeField as varchar(10)
set @timeField = '07:30 PM'

declare @dateField as varchar(10)
set @dateField = '1900-01-01'

select CONVERT(datetime,@dateField + ' ' + CAST(CONVERT(time, @timeField ,121) AS VARCHAR(11)),121)

In your import scripts (I assume you use some sort of SQL to E.T.L your data from Access to SQL server), you can use the convert function as such:

declare @t as time = Convert(time, '10:00PM' )
print @t -- prints 22:00:00.0000000

OR

declare @t as time = Convert(time, '10:00AM' )
print @t -- prints 10:00:00.0000000

And of course if you want to control the precision as per your example:

Convert(time(3), '10:00PM' ) -- prints 22:00:00.000