TimeStamp without milliseconds!!!

  • Hey,

    Am trying to add an timestamp column by using derived column but i dont want to get the milliseconds. i tried using the expression which is mentioned above but am unable to ignore the failure, it again goes back to "fail component" . can u help me with the expression?

    Thanks,

  • Not sure about the question.But if it is datetime column, you can use

    Covert(char(19),date, 120)

  • Sorry about that!

    I need an expression to write in derived column transformation to get the data and time but time only till seconds i dont need milliseconds.

  • I used this expression as mentioned in one of the post but it dint work for me.

    ""If you set the derived column error output to "Ignore Failure" on truncation then you can use

    (DT_DBTIMESTAMP2,3)(DT_DBTIMESTAMP2,0)GETDATE()""

    but am unable to ignore the failure...

  • Then use-

    Convert(char(10),getdate(), 120) -- for date

    Convert(char(8),getdate(),114)-- For time

    Replace getdate(), with our column name.

  • select

    a.*,

    TruncateMillseconds =

    dateadd(ms,-datepart(ms,a.DT),a.DT)

    from

    ( -- Test data

    select DT = getdate()union all

    select DT = '2012-06-20 23:00:56.397'

    ) a

    Results:

    DT TruncateMillseconds

    ----------------------- -----------------------

    2013-05-20 23:02:40.997 2013-05-20 23:02:40.000

    2012-06-20 23:00:56.397 2012-06-20 23:00:56.000

  • Thanks SSCrazy...But i want an expression to write in the derived column of ssis.

    Am adding Timestamp Column from derived column into the table.

    Do u have an expression fro derived column?? right now am just using getdate()

  • In SSIS you can use the DATEPART function. So, even though it would be a little more code that you might want, you can concatenate all the separate parts you want. (I'm fairly certain there is a better way though)

    (DT_WSTR,2)DATEPART( "hh",GETDATE() ) + ":" +(DT_WSTR,2)DATEPART( "mi",GETDATE() ) + ":" +(DT_WSTR,2)DATEPART( "ss",GETDATE() )

    As far as I am aware, there isn't a built in function which will convert your date time (with miliseconds) to a date time to only seconds. Like the following would. :

    SELECT CONVERT(VARCHAR(20), GETDATE() ,20)

    Personally, I'd put the whole datetime stamp into the end table then run some further conversion against it if SSIS can't do it.

    See http://msdn.microsoft.com/en-us/library/ms137586.aspx for a little more on this

  • shrsan (5/21/2013)


    Thanks SSCrazy...But i want an expression to write in the derived column of ssis.

    Am adding Timestamp Column from derived column into the table.

    Do u have an expression fro derived column?? right now am just using getdate()

    Based on DATEPART and DATEADD for SSIS in MSDN the following is what you want to use in an expression in SSIS:

    DATEADD("Ms", -1 * DATEPART("Ms", GETDATE()), GETDATE())

    Not having worked with SSIS for several years, I will leave it to you to determine exactly how this expression should be written to accomplish the task you are working on using a derived column in SSIS.

Viewing 9 posts - 1 through 8 (of 8 total)

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