SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TimeStamp without milliseconds!!!


TimeStamp without milliseconds!!!

Author
Message
shrsan
shrsan
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 117
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,
Neeraj Dwivedi
Neeraj Dwivedi
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2065 Visits: 1341
Not sure about the question.But if it is datetime column, you can use
Covert(char(19),date, 120)
shrsan
shrsan
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 117
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
shrsan
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 117
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
Neeraj Dwivedi
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2065 Visits: 1341
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
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14899 Visits: 11848
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
shrsan
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 117
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
chris.rogers00
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 Visits: 187
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
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97197 Visits: 38988
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search