3

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
doubleJ
  • 1,190
  • 1
  • 16
  • 29
  • possible duplicate of [How to combine date from one field with time from another field - MS SQL Server](http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server) – Ken White Oct 05 '11 at 19:29
  • I was looking at that as the second step in the process. I was wanting to get the date in the appropriate format so I could use that information to combine the two fields. – doubleJ Oct 05 '11 at 19:38
  • **WHAT** database and which version?? **SQL** is just the Structured Query Language - a language used by **many** database systems - **SQL** is **NOT** a database product... stuff like this is very often **vendor-specific** - so we really need to know what **database system** you're using.... – marc_s Oct 05 '11 at 20:25

3 Answers3

1

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))
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Ok... SELECT cast([time] as time(7)) FROM [Sermons].[dbo].[TblSermon] did show the time in the appropriate fashion. But that is just a query, would I need to do an update query to actually change those values in the table? – doubleJ Oct 06 '11 at 15:18
  • 1
    @doubleJ, you can update the two string date and time columns into one datetime column like that `update tablename set datetimecolumn = convert(datetime,datestring + ' ' + CAST(CONVERT(time, timestring ,121) AS VARCHAR(20)),121) ` – Mahmoud Gamal Oct 06 '11 at 15:39
  • Hehehe...That worked perfectly, except for the records that I manually set the time portion of the date field. It looks like it added the time field to the pre-existing data. – doubleJ Oct 06 '11 at 16:01
  • Actually, it was worse. It change the date to the next day if it passed 24 hours. Hehehe...Live and learn. – doubleJ Oct 06 '11 at 16:07
1

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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Viking
  • 11
  • 2
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Oct 05 '11 at 20:25
0

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
Anas Karkoukli
  • 1,342
  • 8
  • 13
  • He doesn't say which version of sql, so time may not be supported. – automatic Oct 05 '11 at 19:27
  • Apologies...It's Ms Sql 2008 R2 – doubleJ Oct 05 '11 at 19:39
  • No worries, then you should have no problem with the proposed solution. – Anas Karkoukli Oct 05 '11 at 19:45
  • Edit AM to PM in Convert(time(3), '10:00AM' ) -- prints 22:00:00.000 :) – Vinay Oct 05 '11 at 20:50
  • I didn't manually create a script, if one was used. I exported a query from access 2010 into an access 2003 database, transferred that database to the sql server, right-clicked on the appropriate database, and chose Tasks - Import Data (selecting access, the file, etc...). – doubleJ Oct 06 '11 at 15:15
  • Then you need to write a simple script that runs, post-import process to reformat the columns as desired. I assume there is no impediment to prevent this. If so, please give me details. – Anas Karkoukli Oct 06 '11 at 15:21