format datetime column

Hello,

I am wondering if someone could help me with formatting datatime column.

Goal - keep column in datatime instead of convert to varchar

Current - '2006-06-21 16:54:33.000'

Wants - '2006-06-21 16:54:33'

Any help is appreciated!

-Lawrence

Code Bits

declare @.time datetime

set @.time = '2006-06-21 16:54:33.000'

select @.time, convert(varchar(255), @.time, 20)

You usually do not want to format the output in the database. This would lead to the question why do you need to do this in T-SQL?

The front-end should be able to format the date to a proper format instead of relying on the database to do it. The database gives the data and the front-end presents it. Skipping the milliseconds are most likely the task of the front-end.

|||

I know what you are saying, but the reason is that the format from this t-sql goes into a transformation and oddly enough the transformation does not accept datetime format with millisecond. I thought SET DateFormat would be able to set any datetime format, but I guess that feature is very limited.

-Lawrence

|||declare @.time datetime
set @.time = '2006-06-21 16:54:33.000'
select @.time, convert(varchar(19), @.time, 121)|||What transformation are you referring to? Is it a DTS/SSIS package? If so that can format the data accordingly. Please elaborate on your problem. The datetime value is stored in native format on the server and if you want to format it as string you need to use CONVERT & other string functions to do it. Typically you will send the values as is to the client and format on the client side using richer mechanisms.