SSIS is changing the hour part of datetime returned from a stored procedure

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 12, 2020 at 7:57 pm

  #3733759

  I have a an ssis package where I am calling a stored proc from. When I run the stored proc in ssms the value returned is '2020-03-06 16:41:47.040'. When I run the sproc in an execute sql task and set the result to an ssis variable of type datetime it changes the value to 3/6/2020 4:41:47 PM inside the ssis package cutting off the milliseconds. I need the milliseconds to remain. Is there a way to stop ssis from dropping off the milliseconds?

• Phil Parkin

  SSC Guru

  Points: 247120

  More actions

  March 12, 2020 at 8:59 pm

  #3733785

  Try using DT_DBTIMESTAMP as your SSIS datatype.

  ---

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 12, 2020 at 9:42 pm

  #3733801

  I'm saving the value in an ssis variable and the only date datatype is Datetime. I tried setting it as string and have the same issue.

• Phil Parkin

  SSC Guru

  Points: 247120

  More actions

  March 12, 2020 at 10:00 pm

  #3733805

  OK, it's not very elegant, but using this

  SELECT RightNow = convert(VARCHAR(30),GETDATE(),21)

  and saving to a string worked for me.

  ---

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 13, 2020 at 1:26 pm

  #3734032

  Thanks Phil, that Got'r Dun.