asked on

Remove Seconds & Milliseconds from Date in SSIS

I've come up with the expression below in SSIS to remove the seconds and milliseconds from a sql server datetime field and be left with just the hours and minutes e.g. 09:30:00:000 but it seems a little extreme to say the least.

Can anyone offer suggestions on how to simplify the expression?

(DT_DBTIME2,0)DATEADD("Mi",DATEDIFF("Mi",TransactionDate,(DT_DBTIMESTAMP)DATEDIFF("dd",(DT_DBTIMESTAMP)"1900/01/01",TransactionDate)),(DT_DBTIMESTAMP)DATEDIFF("dd",(DT_DBTIMESTAMP)"1900/01/01",TransactionDate))

ASKER CERTIFIED SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Louis01

declare @testDate datetime = '2013-07-16 13:42:55.607';
select CONVERT(varchar, @testDate, 13) as VarCharDateWithFullTime
select LEFT(CONVERT(varchar, @testDate, 13), 17) as VarCharDateWithHHMMonly
select Convert(dateTime,LEFT(CONVERT(varchar, @testDate, 13), 17)) as DateTimeDateWithHHMMonly

Open in new window

Lee

This will round to the nearest minute as well. If you merely want to truncate then:

cast(convert(nvarchar(17), TransactionDate , 113) as datetime)

monserob

ASKER

Sorry but ideally I was after a solution in SSIS, not a SQL Server solution

Lee

Ok, but it would be a lot simpler to modify your SQL query that gets the data from SQL. I can't help you any more with SSIS.

Jim Horn

>I've come up with the expression below in SSIS to remove the seconds and milliseconds from a sql server datetime field

>I was after a solution in SSIS, not a SQL Server solution

If you have an SSIS solution that runs, and you don't want this in T-SQL, then go with what you have.

Please expand on not wanting 'a SQL Server solution', as SQL Server is your source, T-SQL is used in SSIS all the time, and the solutions the above experts gave you can easily be thrown into a scalar function that can be called from your SSIS package.

monserob

ASKER

I certainly don't want to get into a discussion about the pros and cons of whether to use SSIS or SQL Server to carry out data transformations; I just wanted to know whether an SSIS expert out there could simplify my solution.

I've decided to accept lsavidge's solution of casting the datetime to a smalldatetime in my extraction query and then cast this value in SSIS to a time data type

Remove Seconds &amp; Milliseconds from Date in SSIS

Remove Seconds & Milliseconds from Date in SSIS