SQL Server - Operand type clash: numeric is incompatible with datetimeoffset

Question

i am having issue with passing the data from one table to another due to data type. I tried converting datetimeoffset into date, and inserting into table where i have it as date type and im still getting this error. this is the format of date/time i have: 2018-12-12 13:00:00 -05:00 in one table, and i have to just pars time and insert it into new table. I tried with casting using ,

CAST([from] AS date) DATE_FROM

I can run the query as select and it works but the moment i try to insert the data into other table even if the other table is formatted and prepared as date type i still get the issue.

Here is the table that stored data with datetimeoffset:

[dbo].[tmp_count](
[elements_Id] [numeric](20, 0) NULL,
[content_Id] [numeric](20, 0) NULL,
[element_Id] [numeric](20, 0) NULL,
[element-name] [nvarchar](255) NULL,
[sensor-type] [nvarchar](255) NULL,
[data-type] [nvarchar](255) NULL,
[from] [datetimeoffset](0) NULL,
[to] [datetimeoffset](0) NULL,
[measurements_Id] [numeric](20, 0) NULL,
[measurement_Id] [numeric](20, 0) NULL,
[from (1)] [datetimeoffset](0) NULL,
[to (1)] [datetimeoffset](0) NULL,
[values_Id] [numeric](20, 0) NULL,
[label] [nvarchar](255) NULL,
[text] [tinyint] NULL

And I am trying to cast columns with datetimeoffset to date and time and push it to #tmp1 table with

   SELECT [elements_Id]
  ,[content_Id]
  ,[element_Id]
  ,[element-name]
  ,[sensor-type]
  ,[data-type]
  ,CAST([from] AS date) DATE_FROM
  ,[to]
  ,[measurements_Id]
  ,[measurement_Id]
  ,CAST([from (1)] AS time (0)) TIME_FROM
  ,CAST([to (1)] AS TIME(0)) TIME_TO
  ,[values_Id]
  ,[label]
  ,[text]
  INTO #Tmp1
  FROM [VHA].[dbo].[tmp_count]

  SELECT 
  FROM #tmp1

which gives me the time in format for DATE_FROM as 2018-12-12 and for the DATE_FROM and DATE_TO as 13:00:00 which is exactly what i need.

Now i am trying to splice this table with another table and push it in final table that looks like this:

[dbo].[tbl_ALL_DATA_N](
[serial-number] [nvarchar](255) NULL,
[ip-address] [nvarchar](255) NULL,
[name] [nvarchar](255) NULL,
[group] [nvarchar](255) NULL,
[device-type] [nvarchar](255) NULL,
[elements_Id] [numeric](20, 0) NULL,
[content_Id] [numeric](20, 0) NULL,
[element_Id] [numeric](20, 0) NULL,
[element-name] [nvarchar](255) NULL,
[sensor-type] [nvarchar](255) NULL,
[data-type] [nvarchar](255) NULL,
[DATE_FROM] [date] NULL,
[to] [datetimeoffset](0) NULL,
[measurements_Id] [numeric](20, 0) NULL,
[measurement_Id] [numeric](20, 0) NULL,
[TIME_FROM] [time](0) NULL,
[TIME_TO] [time](0) NULL,
[values_Id] [numeric](20, 0) NULL,
[label] [nvarchar](255) NULL,
[text] [tinyint] NULL

using query below:

INSERT INTO [dbo].[tbl_ALL_DATA_N]
        ([serial-number], 
         [ip-address], 
         [name], 
         [group], 
         [device-type], 
         [measurement_id], 
         TIME_FROM, 
         TIME_TO, 
         [content_id], 
         [elements_id], 
         [element-name], 
         [sensor-type], 
         [data-type], 
         DATE_FROM, 
         [to], 
         [element_id], 
         [measurements_id], 
         [values_id], 
         [label], 
         [text]) 
   SELECT * 
   FROM   [VHA].[dbo].[tmp_sensor_info] A 
   FULL OUTER JOIN #tmp1 B 
                ON 1 = 1

And here is another message im getting: Msg 206, Level 16, State 2, Line 25 Operand type clash: numeric is incompatible with time

Any ideas?

What is the value of `[From]`? And, if it's a date why is it not being stored as a `date`? — Thom A, Dec 13 '18 at 15:18
That error, however, isn't representative of the error you've given us. Your SQL has the datatype `date`, but your error states `datetimeoffset`. — Thom A, Dec 13 '18 at 15:19
And we also need clarification of the data type of the source data, data type of the destination column, and the full text of the error you're receiving. — Eric Brandt, Dec 13 '18 at 15:24
Dates have no format, they are binary values, just like numeric types. The error complains that you tried to mix datetimeoffset and numeric columns. Most likely, `tbl_ALL_DATA_N` and `tmp_sensor_info` have a different column order and `SELECT * FROM [VHA].[dbo].[tmp_sensor_info] A` returns data in the wrong order — Panagiotis Kanavos, Dec 13 '18 at 15:42

score 3 · Accepted Answer · answered Dec 13 '18 at 16:32

The solution, which @PanagiotisKanavos alluded to in the comments, is to explicitly list the columns in your final SELECT * FROM.... The order of the columns in that SELECT statement aren't lining up with the columns you're INSERTing into in the destination table.

You may need to run an ad hoc instance of the query to sort out the column order. And then do yourself a favor for future maintenance and be sure to include a table alias on all of the listed columns so you (or whoever has to look at the code next) can easily find out if data is coming from [VHA].[dbo].[tmp_sensor_info] or #tmp1.

This is just one of many dangers in using SELECT * in production code. There's a ton of discussion on the issue in this question: Why is SELECT * considered harmful?

Also, as long as you're in there fixing up the query, consider meaningful table aliases. See: Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3).

this fixed the issue. Thank you for all your help and advice regarding select statement and aliases — Slavisha, Dec 13 '18 at 17:45

SQL Server - Operand type clash: numeric is incompatible with datetimeoffset

1 Answers1