Error I am getting " Invalid operator for data type"

Question

Post reply

Error I am getting " Invalid operator for data type"

amitabhssinha

SSC-Addicted

Points: 481
More actions
January 22, 2010 at 3:08 am

#218414

Hi there all,
I am getting an error like
"Invalid operator for data type. Operator equals multiply, type equals datetime."
DECLARE @Driver TABLE(DriverName VARCHAR(64), ArrTime SMALLDATETIME, DepartTime SMALLDATETIME, VehicleNo VARCHAR(16), IsLate DATETIME)
INSERT INTO @Driver(DriverName, VehicleNo, DepartTime, ArrTime, IsLate)
Please suggest.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You're missing half of your insert statement. It needs to be one of the following forms.

INSERT INTO ...

SELECT ...

or

INSERT INTO ...

VALUES ...

Other than that, there's nothing I can see wrong, and nothing that could cause that error. Can you post the entire piece of code?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 2

DECLARE @Driver TABLE(DriverName VARCHAR(64), ArrTime SMALLDATETIME, DepartTime SMALLDATETIME, VehicleNo VARCHAR(16), IsLate DATETIME)

INSERT INTO @Driver(DriverName, VehicleNo, DepartTime, ArrTime, IsLate)

SELECT TOP 10 DrvNameMain AS DriverName, ArrTime = @Time,

DepartTime=@DepTime, vehicleNo AS Vehicle,

(CASE WHEN (DATEDIFF(MINUTE,@ExpArrTime,@EntDate))>= 5

THEN (DATEDIFF(MINUTE,@ExpArrTime,@EntDate))

ELSE NULL

END) AS ILate

FROM bltTssVeh

SELECT [DriverName],[VehicleNo],[DepartTime],[ArrTime],[IfLate] FROM @Driver WHERE [IfLate] IS NOT NULL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Could you post the definition of the bltTssVeh table please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 4

Definition of bltTssVeh is as:

USE [Mark]

GO

/****** Object: Table [dbo].[bltTssVeh] Script Date: 01/22/2010 17:29:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[bltTssVeh](

[Veh_Id] [int] NULL,

[VehicleNo] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TssNo] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TssDate] [datetime] NULL,

[RteNo] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DrvNoMain] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DrvNameMain] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DrvNoSub] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DrvNameSub] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LUB] [int] NULL,

[LUO] [datetime] NULL,

[Status] [bit] NULL

) ON [PRIMARY]

richardsouthall Valued Member Points: 65 More actions · Answer 5

Also if you please, how are @time, @deptime, @exparrtime, @entdate declared?

The last select shows [IfLate] - assuming a typo and [IsLate] is correct. I'd kindly request you copy and paste the complete code in question instead of typing it in. It surely makes it easier to reproduce the problem.