insert short date to table

[yan19454]

I have one table module log which have trigger association with that.

When I

insert into [module log]([Employee Name], [DOB], [Module Name],[Access Module Time], [manager login ID])
SELECT [FULL NAME], DOB ,'Back Injury Competency',getdate(), 'abcl' FROM OcchEmp WHERE (Unit LIKE N'5%')

go



if

insert into [module log]([Employee Name], [DOB], [Module Name],[Access Module Time], [manager login ID])
SELECT [FULL NAME], DOB ,'Back Injury Competency','7/6/2006 9:11:27 AM.', 'abcl' FROM OcchEmp WHERE (Unit LIKE N'5%')

it will generate the error , too.



thx.



It gave me the error:



Server: Msg 8115, Level 16, State 2, Procedure CurrentWinModuleInsert, Line 9
Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated.


I think my problem is show to insert the shortdate in table. Althought my date type is shortdate.



if I deleted the trigger;

date in the module log as 2007-06-29 00:00:00.000 the original date enter

format as 7/6/2006 9:11:27 AM.




Here is the trigger:



CREATE trigger CurrentWinModuleInsert
on dbo.[Module Log]
for insert as
--insert on the win module Back Injury Competency only i
if (SELECT COUNT(*)
FROM inserted
WHERE ([inserted].[Module Name]='Back Injury Competency') ) >0
begin
insert into tblCurrentWinTrainingLog(EmplNO,CTDate, QuizName)
select HREMP_adp.EMPLNO, i.[Access Module Time] AS CTDate,i.[Module Name] as QuizName
FROM inserted i INNER JOIN
HREMP_adp ON i.[Employee Name] = HREMP_adp.[FULL NAME] AND i.DOB = HREMP_adp.DOB


end





if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Module Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Module Log]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCurrentWinTrainingLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCurrentWinTrainingLog]
GO

CREATE TABLE [dbo].[Module Log] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Employee Name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOB] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Module Name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Access Module Time] [datetime] NULL ,
[Manager Login ID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblCurrentWinTrainingLog] (
[EmplNO] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CTDate] [datetime] NULL ,
[PTDate] [datetime] NULL ,
[QuizName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModuleName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

 

[gmmastros]

[tt][blue]nsert into [module log]([Employee Name], [DOB], [Module Name],[Access Module Time], [manager login ID])
SELECT [FULL NAME], DOB ,'Back Injury Competency','[!]7/6/2006 9:11:27 AM.[/!]', 'abcl' FROM OcchEmp WHERE (Unit LIKE N'5%') [/blue][/tt]

You have a period after the AM, which is causing the problem.

-George

"the screen with the little boxes in the window." - Moron