Get rid of decimal seconds from datetime export

[MrJRW]

Good Afternoon

As part of a SSIS package, I have a task that exports a table to a text file. One of the fields is a datetime data type.

When exported, the datatime data is being stored as "YYYY-MM-DD HH:MM:SS.SSSSSSSSS" That's correct; nine decimal seconds.

I need to import this text file into an Access database. The decimal seconds are causing an import error.

? What would be the "best" way to just have interger seconds; HH:MM:SS ?

Thanks
JRW

 

[gmmastros]

This uses the GetDate() function, but shows you how this works. Make sure this returns what you want it to, and if it does, then just implement this in your code by replacing the GetDate() function with your actual column name.

Select DateAdd(Millisecond, -1 * DatePart(Millisecond, GetDate()), GetDate())

You could also use the smalldatetime data type, but this would remove the seconds portion also, which I think you probably don't want. Ex:

[tt][blue]
Select Convert(SmallDateTime, GetDate())
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom