Dates Are Missing Seconds

Question

Post reply

Dates Are Missing Seconds

Ant-Green

SSC Guru

Points: 113507

April 24, 2023 at 8:34 pm

Answer

#4178812

Or where you do this

DECLARE @RECORD_ADD_DT datetime
SET @RECORD_ADD_DT = @RECORD_ADD_DATE

do this instead

DECLARE @RECORD_ADD_DT varchar(26)
SET @RECORD_ADD_DT = convert(varchar(26),@RECORD_ADD_DATE,121)

Viewing 6 posts - 16 through 20 (of 20 total)

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

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

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 do this testing and that the data you show us is not old data!!!

This reply was modified 1 year, 7 months ago by frederico_fonseca.

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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 a syntax issue I don’t know.

Regional settings doing some funky stuff with dates to strings etc.

Can you change the code to set a variable and then set that to be the dynamically built string, then select the string, then execute the string.

Declare @sql nvarchar(max)

Set @sql = 'BULK INSERT #TEMP_TABLE
  FROM ''' + 
 @FILEPATH + 
 ''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);

UPDATE #TEMP_TABLE
SET UNDERLYING_SYMBOL = ''SPX''
WHERE TRIM(UNDERLYING_SYMBOL) LIKE ''^SPX''
 
INSERT INTO DBO.OptionsEOD
SELECT *, ''' + convert(varchar(26),@RECORD_ADD_DATE, 121) + ''' AS TIME_STAMP
FROM #TEMP_TABLE'

Select @sql
Exec (@sql)

What does the select show, both with and without the convert?

water490 SSC Eights! Points: 955 More actions · Answer 3

frederico_fonseca wrote:

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 do this testing and that the data you show us is not old data!!!

there are no db triggers. i just checked.

yes I am truncating the table each time I am testing.

water490 SSC Eights! Points: 955 More actions · Answer 4

Ant-Green wrote:

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 a syntax issue I don’t know.
Regional settings doing some funky stuff with dates to strings etc.
Can you change the code to set a variable and then set that to be the dynamically built string, then select the string, then execute the string.
Declare @sql nvarchar(max)

Set @sql = 'BULK INSERT #TEMP_TABLE
  FROM ''' + 
 @FILEPATH + 
 ''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);

UPDATE #TEMP_TABLE
SET UNDERLYING_SYMBOL = ''SPX''
WHERE TRIM(UNDERLYING_SYMBOL) LIKE ''^SPX''
 
INSERT INTO DBO.OptionsEOD
SELECT *, ''' + convert(varchar(26),@RECORD_ADD_DATE, 121) + ''' AS TIME_STAMP
FROM #TEMP_TABLE'

Select @sql
Exec (@sql)
What does the select show, both with and without the convert?

i tried this. i ran the ssis package and the table optionsEOD is empty. this means that none of the files from the server got downloaded or imported.

This reply was modified 1 year, 7 months ago by water490.

water490 SSC Eights! Points: 955 More actions · Answer 5

Ant-Green wrote:

Or where you do this

DECLARE @RECORD_ADD_DT datetime
SET @RECORD_ADD_DT = @RECORD_ADD_DATE

do this instead

DECLARE @RECORD_ADD_DT varchar(26)
SET @RECORD_ADD_DT = convert(varchar(26),@RECORD_ADD_DATE,121)

it works now. I am seeing seconds!! thank you to everyone who helped me with this!! you guys are awesome!

the problem was that SS was doing an implicit type casting of some kind that was resulting in loss of precision. this was a great learning opp.

matching:

Screenshot 2023-04-24 140040

revised script:

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

DECLARE @RECORD_ADD_DT varchar(26)
SET @RECORD_ADD_DT = convert(varchar(26),@RECORD_ADD_DATE,121)

DROP TABLE IF EXISTS #TEMP_TABLE;

CREATE TABLE #TEMP_TABLE
(
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
more fields
);

Exec ( 'BULK INSERT #TEMP_TABLE
  FROM ''' + 
 @FILEPATH + 
 ''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);

UPDATE #TEMP_TABLE
SET UNDERLYING_SYMBOL = ''SPX''
WHERE TRIM(UNDERLYING_SYMBOL) LIKE ''^SPX''
 
INSERT INTO DBO.OptionsEOD
SELECT *, ''' + @RECORD_ADD_DT + ''' AS TIME_STAMP
FROM #TEMP_TABLE'
)

DROP TABLE #TEMP_TABLE