DATETIME Issue

Hi all

I am having a problem with how the DATE field of a column is being displayed. What I am trying to do is run a DTS that takes the info from a table and places it into a flat file. Of course the format of the flat file does not need the trailing 23:59:59.993. When running a view it gives us the proper format of mm/dd/yyyy but the rest is not being shown in the view. A Cast function in the view into a VARCHAR changes it to something like Oct, 10, 2006. And of course the LEFT function comes up with the same Oct, 10, 2006 format.

How would I tell SQL server to just grab just 10/06/2006 and nothing else or without it trying to change it to a different format?

ThanksLook up the Convert function in BOL. There are a number of formats to display a date in.|||Wonderful, thanks!

DateDiff

I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that??
tia,
e3wittI'd use:SELECT Convert(CHAR(5), DateAdd(min
, DateDiff(min, start, stop), '00:00:00')
, 108)-PatP|||thanks! that works like a charm....|||I'd use:SELECT Convert(CHAR(5), DateAdd(min
, DateDiff(min, start, stop), '00:00:00')
, 108)-PatP

[shameless post]

Thank GOD!

Someone is taking all the difficult questions...

Where would we be otherwise?

OH that's right x002548 is awol

Now will somone PLEASE fix me a 'rita

[/shameless post]|||This is the code PaulYoung posted some time ago, as as such became public domain.
if object_id('dbo.fn_ElapsedTime') is not null
drop function dbo.fn_ElapsedTime
go
/*
************************************************** **************
Created : 06/01/2004
By : Paul Young (dbForums participant)
Purpose : Return elapsed time in 99:99:99.999 format.
Execute : select dbo.fn_ElapsedTime(@.date_start, @.date_end)
************************************************** **************
*/
create function dbo.fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null) returns varchar(40)
as begin
declare @.d int,
@.h int,
@.m int,
@.s int,
@.ms int,
@.dif1 int,
@.ret varchar(40)
select @.d = 0,
@.h = 0,
@.m = 0,
@.s = 0,
@.ms = 0
set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + '.' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end