Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TimeStamp without milliseconds!!! Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 3:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 8:31 PM
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,
Post #1454754
Posted Monday, May 20, 2013 3:16 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:42 AM
Points: 743, Visits: 1,066
Not sure about the question.But if it is datetime column, you can use
Covert(char(19),date, 120)
Post #1454758
Posted Monday, May 20, 2013 3:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 8:31 PM
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.
Post #1454759
Posted Monday, May 20, 2013 3:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 8:31 PM
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...
Post #1454760
Posted Monday, May 20, 2013 3:28 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:42 AM
Points: 743, Visits: 1,066
Then use-

Convert(char(10),getdate(), 120) -- for date
Convert(char(8),getdate(),114)-- For time

Replace getdate(), with our column name.
Post #1454763
Posted Monday, May 20, 2013 9:03 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
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


Post #1454792
Posted Tuesday, May 21, 2013 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 8:31 PM
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()


Post #1455076
Posted Tuesday, May 21, 2013 10:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:30 AM
Points: 73, Visits: 152
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
Post #1455112
Posted Tuesday, May 21, 2013 10:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 22,504, Visits: 30,209
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.



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)
Post #1455113
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse