Obtaining the time from datetime field

From this value: 2002-11-22 00:00:22.000
I want to obtain in string type "00:00:22.000" including miliseconds
I try this but returns: 0:0:22:0
select
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
from cargaprocesos
Can you help me?
Thanks a lot,Enric, try:
convert(varchar(12), dt, 114)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||You should go with that:
Select Convert(varchar(12), GETDATE(), 114)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||Thanks Itzik, I had tried this:
select substring(convert(varchar(50),duracionul
t,113),13,12),
duracionult
from cargaprocesos
But it was failing.
regards,
"Itzik Ben-Gan" wrote:
> Enric, try:
> convert(varchar(12), dt, 114)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Enric" wrote:
>|||A disign suggestion:
I would create a function for this logic.
Makes the code more readable and it makes the logic reusable aswell.
Hope this is helpfull.
Marcel van Eijkel
( www.vaneijkel.com )