Milliseconds are missing from DateTime variable in SSIS Package?

All topics about maintaining SQL Server from a DBA perspective.
Post Reply
Allan
Posts: 7
Joined: Fri Jul 05, 2019 3:42 pm

Mon Jul 08, 2019 6:19 pm

Hi, i have a problem.

I am using a 'DateTime' variable in an SSIS package however it appears that the milliseconds are missing from the date / time in the variable.

Does anybody know how to fix this?

Thanks.
Sergios
Posts: 2
Joined: Mon Jul 08, 2019 6:24 pm

Mon Jul 08, 2019 6:26 pm

Hi,

You need to set the package variable as String data type instead.

You then need to convert the SQL datetime value to a string within the ‘Execute SQL Task’ for example:

Code: Select all

SELECT CONVERT(VARCHAR(23), max([InsertedDateTime]), 121) AS [MaxInsertedDateTime]
FROM [Schema].[Table]
Now the value can simply be converted back to datetime within an OLEDB source:

Code: Select all

SELECT [Column]
FROM [Schema].[Table]
WHERE [InsertedDateTime] > CONVERT(DATETIME, ?, 121)
Allan
Posts: 7
Joined: Fri Jul 05, 2019 3:42 pm

Mon Jul 08, 2019 6:27 pm

Thank you @Sergios!
Post Reply

Social Media