CONVERT / CAST

[le1234]

Can anyone help with the diference I am getting when running these two queries. I have a duration field that is stored in miliseconds which I am trying to convert - but the deurations come out different if I use CONVERT or CAST.

SELECT dbo.actuals.TaskId,
dbo.actuals.Who,
CONVERT(varchar(2),FLOOR(dbo.actuals.Duration / 3600000)) + ':' + CONVERT(varchar(2),(FLOOR(dbo.actuals.Duration / 3600000 - FLOOR(dbo.actuals.Duration / 3600000)) * 60)) AS Duration,
dbo.actuals.JournStart,
dbo.actuals.JournEnd,
dbo.tasks.TaskName
FROM dbo.actuals INNER JOIN
dbo.mans ON dbo.actuals.Who = dbo.mans.Who INNER JOIN
dbo.tasks ON dbo.actuals.TaskId = dbo.tasks.TaskId
WHERE dbo.tasks.TaskName like '%liaison%'
ORDER BY dbo.actuals.JournStart DESC,
dbo.tasks.TaskName DESC


TaskId Who Duration Start End TaskDetail
278 706 1:0 28/04/2006 28/04/2006 Research / Liaison
291 19 0:0 28/04/2006 28/04/2006 Research / Liaison
278 706 1:0 28/04/2006 28/04/2006 Research / Liaison
279 706 0:0 28/04/2006 28/04/2006 Research / Liaison
286 21 0:0 28/04/2006 28/04/2006 Research / Liaison
265 21 0:0 28/04/2006 28/04/2006 Customer Liaison



SELECT dbo.actuals.TaskId,
dbo.actuals.Who,
CAST(FLOOR(dbo.actuals.Duration / 3600000) AS varchar(2)) + ':' + CAST(FLOOR((dbo.actuals.Duration / 3600000 - FLOOR(dbo.actuals.Duration / 3600000)) * 60) AS varchar(2)) AS Duration,
dbo.actuals.JournStart,
dbo.actuals.JournEnd,
dbo.tasks.TaskName
FROM dbo.actuals INNER JOIN
dbo.mans ON dbo.actuals.Who = dbo.mans.Who INNER JOIN
dbo.tasks ON dbo.actuals.TaskId = dbo.tasks.TaskId
WHERE (dbo.tasks.TaskName LIKE '%liaison%')
ORDER BY dbo.actuals.JournStart DESC, dbo.tasks.TaskName DESC

TaskId Who Duration Start End TaskDetail
278 706 1:57 28/04/2006 28/04/2006 Research / Liaison
291 19 0:57 28/04/2006 28/04/2006 Research / Liaison
278 706 1:0 28/04/2006 28/04/2006 Research / Liaison
279 706 0:42 28/04/2006 28/04/2006 Research / Liaison
286 21 0:40 28/04/2006 28/04/2006 Research / Liaison
265 21 0:30 28/04/2006 28/04/2006 Customer Liaison

 

[xlbo]

what's this got to do with Reporting Services ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[le1234]

its a report i am doing in reporting services

 

[Sheco]

Hi,

It seems like this is more of a SQL Programming question than a Reporting Services question. There is another tek-tips forum that is perhaps more on point to your question: forum183