CODEWITHSUNDEEP
sqlms-access
doubleJ
doubleJ

Reputation: 1216

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 date 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.

Upvotes: 3

Views: 1456

Answers (3)

Anas Karkoukli
Anas Karkoukli

Reputation: 1342

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

Upvotes: 0

Viking
Viking

Reputation: 11

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)

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

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))

Upvotes: 1

Related Questions