decimal to time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

decimal to time

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-25 : 09:40:26

how to convert decimal to time

ex
8.30 to 8:30

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-10-25 : 09:45:42

What does 8.6 or 8.9 or 75.79 convert to?

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-25 : 09:51:08

its decimal no that id number like 8.33 i want to show it as 08:30 in time format using sql server

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-25 : 10:05:09

Try This -


DECLARE @Number AS NUMERIC(18,2)
SELECT @Number = 8.33

SELECT CASE WHEN LEN(LEFT( CONVERT(VARCHAR(10), @Number), CHARINDEX('.', CONVERT(VARCHAR(10), @Number))-1 )) = 1 THEN 
	'0'+ LEFT( CONVERT(VARCHAR(10), @Number), CHARINDEX('.', CONVERT(VARCHAR(10), @Number))-1 ) END
	+ ':' + 
CASE WHEN LEN(RIGHT( CONVERT(VARCHAR(10), @Number), LEN(CONVERT(VARCHAR(10), @Number)) - CHARINDEX('.', CONVERT(VARCHAR(10), @Number)))) = 1 THEN 
	RIGHT( CONVERT(VARCHAR(10), @Number), LEN(CONVERT(VARCHAR(10), @Number)) - CHARINDEX('.', CONVERT(VARCHAR(10), @Number))) + '0' ELSE 
RIGHT( CONVERT(VARCHAR(10), @Number), LEN(CONVERT(VARCHAR(10), @Number)) - CHARINDEX('.', CONVERT(VARCHAR(10), @Number))) END

I could not understand why 8.33 to 8.30 instead of 8.33 ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-25 : 10:13:12

8.30 is decimal number right
i want to convert it to time format like 08:30

u getting me

one table is having

empid timein

1 8.3
2 8.45

i want to select this data and put in to another table

say attendance

id timein timeout
1 08:30
2 08:45

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-25 : 10:20:34

quote:
Originally posted by pnasz

its decimal no that id number like 8.33 i want to show it as 08:30 in time format using sql server

I was talking about your this reply
probably this might be typo mistake as per your next reply.
anyways you can straight away use the script above by replacing
@number with original column name.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-25 : 15:27:08

This may not work 100%, but you can try it:

DECLARE @Number AS NUMERIC(18,2)
SELECT @Number = 8.33

SELECT CAST(RIGHT('0' + REPLACE(CAST(@Number AS VARCHAR(10)), '.', ':'), 5) AS TIME)

Devart
Posting Yak Master

102 Posts

Posted - 2010-10-26 : 02:44:30

Hello,

You can try this for SQL Server 2008:

DECLARE @x decimal(18,2)
SET @x = 0.59

SELECT cast(replace(cast(@x as varchar(5)),'.',':') as time);

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-26 : 03:26:31

[code]DECLARE @Sample SMALLMONEY = 8.3

SELECT CAST(CAST(FLOOR(@Sample) AS INT) AS VARCHAR(12)) + ':' + CAST(CAST(100 * (@Sample - FLOOR(@Sample)) AS INT) AS VARCHAR(12))[/code]

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-26 : 10:35:06

DECLARE @Sample SMALLMONEY = 8.3

select PARSENAME(@sample,2)+':'+PARSENAME(@sample,1)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-26 : 10:36:35

or

select REPLACE(@sample,'.',':')

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-26 : 10:41:50

quote:
Originally posted by pnasz

8.30 is decimal number right
i want to convert it to time format like 08:30

u getting me

one table is having

empid timein

1 8.3
2 8.45

i want to select this data and put in to another table

say attendance

id timein timeout
1 08:30
2 08:45

What do you want for these?

7:77
8:92

Madhivanan

Failing to plan is Planning to fail

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-10-26 : 11:32:14

quote:
Originally posted by pnasz
its decimal no that id number like 8.33 i want to show it as 08:30 in time format using sql server

Aren't 8.6 or 8.9 or 75.79 decimal numbers?

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-27 : 03:17:31

I think OP is suppose to change values into time
by mistake i think they have been chosen decimal for time so they are storing time 08:33 as 8.33.
There will not be chance for having the values like 75.79.
Let OP reply for this.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-10-27 : 06:37:16

quote:
Originally posted by vaibhavktiwari83

I think OP is suppose to change values into time
by mistake i think they have been chosen decimal for time so they are storing time 08:33 as 8.33.
There will not be chance for having the values like 75.79.

You may well be right but it is only an assumption. Given the minimal information provided it's hard for anyone to provide any sort of solution without making a lot of assumptions which can easily turn out to be incorrect, thereby wasting people's time, that is the point I (and others) was trying to make, perhaps I was being too subtle.

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources