The Cast() Function

MYSQL: Converting time datatype to DECIMAL

[dbarasuk]

Hey,
If I do SELECT CAST("09:00:00" AS DECIMAL(3,2));
I get 9.00, which is correct . Doing SELECT CAST("09:30:00" AS DECIMAL(3,2)) gives the same result. But What I wanted was to output something such as 9.5 since 30 minutes represent half an hour.

How can I solve that enigma?

NOTE: This function is being applied to a time data type column. It represents the length of time performed by an employee per day, which will be multiplied by a decimal salary rate per hour in another column to find a daily wage of the employee on the fly in a next column.

Kind regards,
D.B

[LifeinAsia]

Probably better to use:
SELECT DatePart(hour,09:30:00") + DatePart(minute,09:30:00")/60 AS WorkHours

[dbarasuk]

It doesn't work. Is DatePart() a custom MYSQL function?
Thanks

[coopster]

SELECT CAST(HOUR('09:30:00') + MINUTE('09:30:00')/60 AS DECIMAL(5,2));

[LifeinAsia]

It doesn't work. Is DatePart() a custom MYSQL function?

Oops- sorry. I thought you originally said MS SQL. My eyes are apparently going...

[dbarasuk]

Dear Coopster,
thanks, it works as expected. However I need a bit of more light for The DECIMAL(M,D) data type. I have never understood what M stands for, but it's clear for D. If I do SELECT CAST(5 AS DECIMAL(3,2)); This yields 5.00. But SELECT CAST(5 AS DECIMAL(5,2); also yields the same result, i.e 5.00. Apparently there is no difference.

But When I used the expression you just used to reply to the topic, using DECIMAL(3,2) just gave me a value in some of my columns 9.99 (this was the daily work time). But using yours, just gave me a value such 10.00(this is more reasonable work time) which is what I was expecting.

So how can I understand this?

Thanks

[coopster]

M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the "-" sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the “-” sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10. UNSIGNED, if specified, disallows negative values.

Resource:
[dev.mysql.com...]

So if you wanted to be able to hold a maximum value of 1,234,567.89 you would specify DECIMAL(9,2). The reason I increased your value to 5,2 was so that you could hold more than 9 hours in your calculated daily hours. Now you can hold 999.99 hours. Granted, we don't have that many hours in a day, but it certainly won't hurt to leave it there in case you copy/paste your code for a weekly wage calculation or something and forget to bump up the DECIMAL precision. So, if it is only going to be a daily calculation, you could use 4,2 instead.

[dbarasuk]

thanks dear; it's very clear now