June 19, 2008 at 10:59 pm
hi,
I have created a package for transfering excel sheet values to the sql tables .In that i want to convert the total timeworked column values as a float value in sql table,since i want to use it as a measure in the cube.So i must made the conversion,either in the excel or using the SSIS component.
Help me out guys.
Thanks & Regards
vijay
June 20, 2008 at 12:40 am
Uhmmmm, I would consider using a derived column, and working with some string functions, and then building a concatenated date value from there (something like left, mid and right)
Trust this helps
~PD
June 20, 2008 at 1:25 am
hi,
Could you please give me the expression for the conversion.
Thanks
vijay
June 20, 2008 at 1:29 am
Not sure, readup in BOL in SSIS string expressions
~PD
June 20, 2008 at 1:57 am
hi,
I had converted that using.
REPLACE([Total HRS], ":", ".")
Now i want to convert the datatype into numeric.ANy idea
June 20, 2008 at 2:00 am
I am misunderstanding you
You want to convert 27.12.2008 to numeric. Why would you wanna do that?
Unless you mean that you want to convert 27.12.2008 to 27122008, in which case your initial replace could do the trick (replace ":" with "")
~PD
June 20, 2008 at 5:14 am
hi mate,
Actually my requiremnt is i has to set the Total Hours column as a measure in the cube.For doing that so the column must be a datatype of int or a float.SO i have to convert the time(12:10)to(12.10) and convert the datatype to float.
I had done that using the dataconversion component now..But the round function is not working with the derived column component because i have to round off the column values to two decimal places.
June 20, 2008 at 5:19 am
Gotta say that I have never tried that
Good luck man
June 20, 2008 at 5:30 am
hi,
Still i cant figure out why Round function not working with this..May be we cant round of a float datatype ah.....The expression i used is ROUND([New Total HRS],2)
June 20, 2008 at 7:04 am
The requirement, or perhaps the description of it, sounds a little off to me. Converting a time 12:10 to 12.10 just seems wrong to me. I understand you want a float or int, but that number is technically now incorrect. It'll sum incorrectly also.
I would have thought you'd want to convert 12:10 to 12.1667 (i've dropped accuracy here). Assuming 12:10 is HH:MM then your measure would be 'Time in Hours', and would be accurate and would roll up. Carrying on the same theme, 12:30 would convert to 12.5. Again, 12:40 would convert to 12.6667. But using your method, 12:30 + 12:40 when rolled up in the cube would result in 24:70, which i would argue is incorrect. It should roll to 12:30 + 12:40 == 12.5 + 12.667 = 25.167 which is 25 hrs to .167 of an hour which equals ~ 10 minutes.
To perform this conversion, get the hours part of the datetime field use datepart something like
SELECT DATEPART(hour, your_date_field)
To get the minutes, use the same function. Remembering to convert them to hours (so divide by 60).
SELECT DATEPART(mi, your_date_field) / 60
the final script could look something like
SELECT DATEPART(hour, your_date_field) + (DATEPART(mi, your_date_field) / 60)
Depending on your specific situation, you will need to tweak some of this.
Steve.
June 24, 2008 at 2:02 am
hi steve,
You r exactly correct..Converting to an integer is not a good idea....It gives me wrong results..Now am trying ur idea...:)
vijay
June 24, 2008 at 3:15 am
hi steve,
You r exactly correct..Converting to an integer is not a good idea....It gives me wrong results..Now am trying ur idea...:)
vijay
June 25, 2008 at 5:02 am
Ya steve, you are absolutely correct. I agree with you cent percent. I also thought the same way and good that you think the correct solution to this problem
June 25, 2008 at 6:54 am
hi steve,
In DATEPART function there is no option for getting hours or minutes..It throws me a error.It has only options susch as geeting a day,Month or a year.....Am having a column value of time like 12:12....How to retrieve the time in this case as you haev noted.
The error am getting is
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Addind columns for Day,Month and year [926]]: Attempt to find the input column named "mi" failed with error code 0xC0010009. The input column specified was not found in the input column collection.
Error at Data Flow Task [Addind columns for Day,Month and year [926]]: Attempt to parse the expression "DATEPART(mi, Total HRS)" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
Error at Data Flow Task [Addind columns for Day,Month and year [926]]: Cannot parse the expression "DATEPART(mi, Total HRS)". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Addind columns for Day,Month and year [926]]: The expression "DATEPART(mi, Total HRS)" on "input column "Total HRS" (3044)" is not valid.
Error at Data Flow Task [Addind columns for Day,Month and year [926]]: Failed to set property "Expression" on "input column "Total HRS" (3044)".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Thanks & Regards
vijay
June 25, 2008 at 8:54 am
The Datepart function definitely supports minutes (see here) so the problem may be with your input column data. I was assuming you'd push in a single datetime field, but in retrospect, that doesn't seem lik eit would work. What is the format for the data that you have? Is it a datetime field, or perhaps 2 two fields (start and end). If it's the two, you could possibly use the datediff function and specify that you want the difference (ie the returned result) in minutes, which could make everything easier.
HTH,
Steve.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply