Hi... Can any one tell me how to convert a date which is in this format "2000-11-27 11:24:00" ....the dates are in CST format..I need to convert them to GMT. I hurd that in SQL we have a direct function which converts from CST to GMT .... I need to implement this function in the derived column...while get the data from source to target can any one help me out Thanks in advance

let me know what the GMT format looks like and we will help you on expressionhttp://www.rad.pasfu.com

Thanks for the quick reply Reza... Format is same as CST "2000-11-27 11:24:00" but it should add 6 hours to it

you just need to add 6 hours to it, this is the expression: DATEADD( "HH", 6, InputDateColumn ) in the expression above, you should put your input column instead of InputDateColumn.http://www.rad.pasfu.com

Our Lead is asking to use a function which directly converts from CST to GMT do you have any idea about the SQL function which converts from CST to GMT

there is no expression function for directly changing cst to gmt. and if there be a sql function for this conversion, you can not use that function in derived column transform, you can only use expressions in the derived column transform. and the expression I post in previous post will result gmt simply. why you looks for alternative way?!!!!!http://www.rad.pasfu.com

Thanks alottt for your favourable responses reza.. can you also give me an alternative way ....without using in Derived column is also ok

you need to create a custom function, preferably an inline UDF. This really isn't a SSIS question and it should be moved to the Transact-SQL forum. I'll post the exact the inline function we used to convert from GMT to EST. I'll re-engineer it in the morning. If this gets moved to the appropriate forum, it may get answered before then.Please 'Mark as Answer' if found helpful - Chris@tier-1-support

SQL Server 2008 has a new datatype, datetimeoffset. You can use the SWITCHOFFSET function with this datatype in order to convert times from one zone to another. This is functionality in the SQL Server engine, though - you can't use it in the Derived Column transformation, but you can put the logic in a source query. This post has some good information on it: http://blogs.msdn.com/b/bartd/archive/2009/03/31/the-death-of-datetime.aspx Reza, the expression you provided doesn't take into account differences in whether daylight savings time is observed in both locations, etc. Converting times from one timezone to another is a bit more complicated than just adding or subtracting hours - there are a lot of rules associated with it.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

Reza, the expression you provided doesn't take into account differences in whether daylight savings time is observed in both locations, etc. Converting times from one timezone to another is a bit more complicated than just adding or subtracting hours - there are a lot of rules associated with it. John, I wasn't very familiar with what CST or GMT means and I asked for difference, and I see only 6 hours difference, so I used dateadd to add 6 hours only. but if this should be concerned with daylight saving time and .... there isn't as easy as a simple expression as you said. Regards,http://www.rad.pasfu.com

Use GETUTCDATE() which is available in SSIS and SQL Serevr as well , it will convert your timezone into GMT . In your case it will convert CST to GMT. P:S it handles Daylight savings by itself Cheers , Varun

Try This... SET @liGMTModifier = DATEDIFF(HOUR, GETUTCDATE(), GETDATE()); SET @ldGMTStartDateTime = DATEADD(HOUR, @liGMTModifier, @adStartDateTime);Clayton