TimeStamp without milliseconds!!!

Question

Post reply

TimeStamp without milliseconds!!!

shrsan

SSC Eights!

Points: 900
More actions
May 20, 2013 at 3:05 pm

#293544

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,

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

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

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

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

Covert(char(19),date, 120)

shrsan SSC Eights! Points: 900 More actions · Answer 2

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.

shrsan SSC Eights! Points: 900 More actions · Answer 3

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

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 4

Then use-

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

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

Replace getdate(), with our column name.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

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

shrsan SSC Eights! Points: 900 More actions · Answer 6

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()

chris.rogers00 Right there with Babe Points: 790 More actions · Answer 7

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.