The Cast() Function

Forum Moderators: open

Message Too Old, No Replies

The Cast() Function

MYSQL: Converting time datatype to DECIMAL

dbarasuk

3:24 pm on Jul 25, 2008 (gmt 0)

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

3:44 pm on Jul 25, 2008 (gmt 0)

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

dbarasuk

12:49 pm on Jul 26, 2008 (gmt 0)

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

coopster

1:29 pm on Jul 26, 2008 (gmt 0)

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

LifeinAsia

4:29 pm on Jul 28, 2008 (gmt 0)

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

9:02 pm on Jul 28, 2008 (gmt 0)

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

9:24 pm on Jul 28, 2008 (gmt 0)

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

9:39 pm on Jul 28, 2008 (gmt 0)

thanks dear; it's very clear now