x

insert time into a time(0) data type

I have a MS Access 2000 frontend and a SQL Server 2008 Express front end. I'm trying to insert a new record, and the time(0) data type always gives me problems. If I type this in directly into SQL Server Mgmt Studio query, it works. But the same thing doesn't work in Access.

INSERT INTO  [GCTest05].[dbo].[tblBAIIDActivity] (...      ,[ActivityTime] ...      )
VALUES       (...,'8:00:00 AM',...)
The [ActivityTime] column is a time(0) data type.
more ▼

asked May 11 '11 at 11:08 AM in Default

EuniceH gravatar image

EuniceH
83 8 8 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
Access must not use that function. Could you not use a datetime column and chose getdate()? I don't know much about access.
more ▼

answered May 11 '11 at 11:10 AM

sqlnubi gravatar image

sqlnubi
983 4 9 12

This is correct. Access does not have an equivalent of time data type. However, SQL Server will happily accept a datetime input from your Access query and discard the date part when inserting the time value. For example,

declare @t time(0);
set @t = getdate();
select @t;

works just fine

Please try formatting your query such that the time value looks like a well formatted datetime (or use can use the now function).
May 11 '11 at 11:29 AM Oleg

I've tried this and always get error 3155 : ODBC--insert on a linked table 'tblBAIIDActivity' failed. I've also used the ActivityTime and dteTime as a Date and that doesn't work either, same error. Here's a portion of my code. If I comment out the "!ActivityTime = dteTime" then the insert works.

Public Type BAIIDEvent ... ActivityTime As String ... End Type

Public Sub AddBAIIDEvent(udtBAIIDEvent As BAIIDEvent, Optional fSuccessful As Boolean)

Dim rst As DAO.Recordset Dim dteTime As String ...

  dteDate = udtBAIIDEvent.ActivityDate
  dteTime = udtBAIIDEvent.ActivityTime
... Set rst = gdbTables.OpenRecordset("select * from tblBAIIDActivity where BAIIDID = " & udtBAIIDEvent.BAIIDID & " AND ActivityDate = #" & dteDate & "# AND BAIIDEventID = " & lngEventID, dbOpenDynaset, dbSeeChanges) With rst If .BOF And .EOF Then .AddNew ... !ActivityTime = dteTime .Update ... End If End With
May 11 '11 at 12:55 PM EuniceH
plus I forgot to add that I tried assigning !ActivityTime = Now() and that gave the same error.
May 11 '11 at 12:56 PM EuniceH
@EuniceH Since implicit conversion from time to varchar is allowed, please try to use string but do not specify the AM/Pm part, and format the string as hh:mm:ss meaning include leading zero if needed for any part that is less than 10. For example, to represent 5 past 8 in the morning use "08:05:00" and to represent quarter till 5 in the afternoon use "16:45:00"
May 11 '11 at 01:14 PM Oleg
that worked! I'll have to mess around with it, but at last there is a way to insert! Thanks much Oleg!
May 11 '11 at 01:21 PM EuniceH
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x48
x28

asked: May 11 '11 at 11:08 AM

Seen: 1986 times

Last Updated: May 11 '11 at 11:15 AM