Post reply

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

  • 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?

  • March 12, 2020 at 8:59 pm

    #3733785

    Try using DT_DBTIMESTAMP as your SSIS datatype.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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.

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • March 13, 2020 at 1:26 pm

    #3734032

    Thanks Phil, that Got'r Dun.

Viewing 5 posts - 1 through 4 (of 4 total)

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