Showing posts with label stores. Show all posts
Showing posts with label stores. Show all posts

Friday, March 30, 2012

How can I convert datetime to number of minutes

I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.

Try something like this

(datepart(hh, tblTasks.TaskDuration) * 60) + datepart(mm, tblTasks.TaskDuration)

|||

I tried this and it will return the number of minutes for the hours; however, the Parenthesis will not stay around the (datepart(hh, tblTasks.TaskDuration) * 60) in the view. So the (mm) are not being added.

Well it is adding time for the minutes but 30 is calculating to 10, so 03:30 is returning 190 minutes and it should be 210.

Any ideas?

|||I gave you the wrong datepart signifier, try datepart(n, tblTasks.TaskDuration)|||

Ok, so now that I have the number of minutes, can I convert this to hours and minutes. What I mean is the reporting tool needs a numeric column to sum on, so 03:15 needs to be 3.25.

Is this possible?

|||

I tried and it looks to be returning the correct format. If you have any comments, I appreciate them.

CONVERT (FLOAT, DATEPART(hh, dbo.tblVolunteerTasks.VTaskDuration) * 60 + DATEPART(n, dbo.tblVolunteerTasks.VTaskDuration)) / 60

Wednesday, March 21, 2012

How can I aggregate multilingual Data

Hi All,
I have three database servers which are german, english and french each
stores data in their localized formats of values like 1000.85 in english
1.000,85 in german and french but i have to get these data and aggreate the
results and display a report.
Can any one help regarding how to aggregate localized data and display a
report say in english.
Regards
enzeekayIf your problem is how to get the data from three different servers to appear
in one result set, then the answer is: Use linked servers. On the English
server, create a linked server for each of the other servers. Look in SQL
Server Books Online for how to do this.
You can then do queries like:
SELECT Product, SalesRevenue*BritishExchangeRate FROM MyEnglishTable
UNION
SELECT Product, SalesRevenue*EuroExchangeRate FROM
MyFrenchServer.MyDatabase.dbo.MyFrenchTable
UNION
SELECT Product, SalesRevenue*EuroExchangeRate FROM
MyGermanserver.MyDatabase.dbo.MyGermanTable
I have assumed that it is better to do the conversion rates at the SQL
Server end, but you could also do it in Reporting Services.
The number format is not important. There is only one internal format inside
SQL Server; the fact that when the numbers display, French use a period for a
thousands separator and a comma for a decimal is immaterial. That is just how
the number displays on a French system. The same table copied to an English
system will display accordingly.
HTH
Charles Kangai, MCT, MCDBA
"Girish Kumar" wrote:
> Hi All,
> I have three database servers which are german, english and french each
> stores data in their localized formats of values like 1000.85 in english
> 1.000,85 in german and french but i have to get these data and aggreate the
> results and display a report.
> Can any one help regarding how to aggregate localized data and display a
> report say in english.
> Regards
> enzeekay
>sql