Convert HH,MM to decimal ?

I have a table with a specific column that i get from an AS/400
The column holds worktime specified in HH,MM format. How do i convert
that do a decimal number.
ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
Thankskjo007@.hotmail.com wrote:

> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
select datepart(hour, '2005-01-01 18:45') + cast(datepart(minute,
'2005-01-01 07:45 AM') as decimal) / 60
HTH,
Stijn Verrept.|||>> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
>
> select datepart(hour, '2005-01-01 18:45') + cast(datepart(minute,
> '2005-01-01 07:45 AM') as decimal) / 60
I don't think that will work as "HH,MM" is not a recognised Sql date format
and therefore the date functions you use will not return the expected data..

> HTH,
> Stijn Verrept.
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter wrote:

> I don't think that will work as "HH,MM" is not a recognised Sql date
> format and therefore the date functions you use will not return the
> expected data..
? What do you mean? I tried it out, it works without problems. He
wants the time converted to decimal and that's what this does:
select datepart(hour, '2005-01-01 07:45 AM') + cast(datepart(minute,
'2005-01-01 07:45 AM') as decimal) / 60
Kind regards,
Stijn Verrept.|||
> ? What do you mean? I tried it out, it works without problems. He
> wants the time converted to decimal and that's what this does:
> select datepart(hour, '2005-01-01 07:45 AM') + cast(datepart(minute,
> '2005-01-01 07:45 AM') as decimal) / 60
> --
> Kind regards,
> Stijn Verrept.
>
He said he is receiving the data from AS/400 in the format "HH,MM" and gave
the example "7,45"
Substituting that into your solution gives...
select datepart(hour, '7,45') + cast(datepart(minute, '7,45') as decimal) /
60
... which doesn't work!
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||<kjo007@.hotmail.com> wrote in message
news:1139314990.653609.216860@.f14g2000cwb.googlegroups.com...
>I have a table with a specific column that i get from an AS/400
> The column holds worktime specified in HH,MM format. How do i convert
> that do a decimal number.
> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
> Thanks
declare @.time varchar(5)
set @.time = '7,45'
select cast(left(@.time, charindex(',', @.time)-1) + (right(@.time,
len(@.time) - charindex(',', @.time)))/60.0 as decimal(5,2))|||It may be worth creating a function for this logic if it is needed in more
than one place.
usd_ConvertAS400Time(7,45) will be easier to type, read, and maintain.
However, there may be a performance hit, I'm not really sure.
Then again, changing the column and your AS400 import to store the value
differently may remove more headaches, depending on if you ever use the data
in the current format.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23ZoNr3$KGHA.2276@.TK2MSFTNGP15.phx.gbl...
> <kjo007@.hotmail.com> wrote in message
> news:1139314990.653609.216860@.f14g2000cwb.googlegroups.com...
> declare @.time varchar(5)
> set @.time = '7,45'
> select cast(left(@.time, charindex(',', @.time)-1) + (right(@.time,
> len(@.time) - charindex(',', @.time)))/60.0 as decimal(5,2))
>|||Peter wrote:

> He said he is receiving the data from AS/400 in the format "HH,MM"
> and gave the example "7,45"
> Substituting that into your solution gives...
> select datepart(hour, '7,45') + cast(datepart(minute, '7,45') as
> decimal) / 60
> ... which doesn't work!
Very true, my bad!
HTH,
Stijn Verrept.|||Of course, either way there could be a performance hit.
If he cannot change the table structure...
it may be better (easier/faster) for the poster to do the conversion client
side..
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ejySWSALGHA.532@.TK2MSFTNGP15.phx.gbl...
> It may be worth creating a function for this logic if it is needed in more
> than one place.
> usd_ConvertAS400Time(7,45) will be easier to type, read, and maintain.
> However, there may be a performance hit, I'm not really sure.
> Then again, changing the column and your AS400 import to store the value
> differently may remove more headaches, depending on if you ever use the
> data
> in the current format.
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:%23ZoNr3$KGHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||maybe this:
select replace('7,45', ',', ':')
7:45