Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TimeStamp without milliseconds!!!


TimeStamp without milliseconds!!!

Author
Message
shrsan
shrsan
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
SSC Eights!
SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)

Group: General Forum Members
Points: 905 Visits: 1319
Not sure about the question.But if it is datetime column, you can use
Covert(char(19),date, 120)
shrsan
shrsan
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
SSC Eights!
SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)

Group: General Forum Members
Points: 905 Visits: 1319
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3262 Visits: 11771
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
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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 Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24255 Visits: 37978
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