thank you for that!

The source table had a different level of precision.  Once I matched the destination so it matched the source it worked.

thank you everyone for the reply.  There is no error message on why it failed so that is why it is so puzzling.  I am continuing to investigate to see...

it works now.  I am seeing seconds!! ...

Still think it’s part of the insert in the proc as the convert to char needs to be outside of the dynamic SQL, as for why it was giving...

• This reply was modified 2 years, 4 months ago by water490.

I would say that you have a trigger on that table that is removing seconds from the column.

and I also assume you are clearing that table in order to...

np

here you go

np

here you go:

stored proc:

ALTER PROCEDURE [dbo].[UpdateOptionsEOD] (@FILENAME varchar(200), @RECORD_ADD_DATE datetime)
AS

DECLARE @FILEPATH VARCHAR(200)
SET @FILEPATH = @FILENAME

--DECLARE @RECORD_ADD_DT datetime
--SET @RECORD_ADD_DT = @RECORD_ADD_DATE

DROP TABLE IF EXISTS #TEMP_TABLE;

CREATE TABLE #TEMP_TABLE
(
[UNDERLYING_SYMBOL] [nvarchar](10)...

I figured out the problem:

SELECT *, convert(datetime, convert(char(26), ''' + @RECORD_ADD_DT + ''', 121) , 121) AS TIME_STAMP
FROM #TEMP_TABLE'

The "" were in the wrong spot.

The time stamp...

thank you everyone.  I am getting error when I update the dynamic sql.  see below.  Any idea on what is wrong?

Good answer, makes complete sense, thanks for taking the time to write it out.

There are a couple of points I'd like to mention, however.

First is that SFTP is completely...

Taking a step back for a moment ...

It seems that you are running an SSIS package which calls a proc which runs BULK INSERT – a rather convoluted design....

• This reply was modified 2 years, 4 months ago by water490.

I am getting this error.  How do I fix it?

(573 rows affected)
Msg 468, Level 16, State 9, Line 14
Cannot resolve the collation conflict between "Latin1_General_CI_AS"...

• This reply was modified 2 years, 5 months ago by water490.

drop table if exists #trades;
go
create table #trades(
  underlying_symbol         varchar(10) not null,
  trade_date    ...

Thank you both.  The query works now.

thank you for this.

I am a rookie so excuse the ignorance.  I need to re-calculate the index after a certain calculation is done.   I thought that using the script...