SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 decimal to time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnasz
Posting Yak Master

101 Posts

Posted - 10/25/2010 :  09:40:26  Show Profile  Reply with Quote
how to convert decimal to time

ex
8.30 to 8:30

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 10/25/2010 :  09:45:42  Show Profile  Reply with Quote
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
Go to Top of Page

pnasz
Posting Yak Master

101 Posts

Posted - 10/25/2010 :  09:51:08  Show Profile  Reply with Quote
its decimal no that id number like 8.33 i want to show it as 08:30 in time format using sql server

Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 10/25/2010 :  10:05:09  Show Profile  Reply with Quote
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
Go to Top of Page

pnasz
Posting Yak Master

101 Posts

Posted - 10/25/2010 :  10:13:12  Show Profile  Reply with Quote
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

Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 10/25/2010 :  10:20:34  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/25/2010 :  15:27:08  Show Profile  Reply with Quote
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)
Go to Top of Page

Devart
Posting Yak Master

102 Posts

Posted - 10/26/2010 :  02:44:30  Show Profile  Visit Devart's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/26/2010 :  03:26:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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))



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/26/2010 :  10:35:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

DECLARE @Sample SMALLMONEY = 8.3

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/26/2010 :  10:36:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
or

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/26/2010 :  10:41:50  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 10/26/2010 :  11:32:14  Show Profile  Reply with Quote
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
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 10/27/2010 :  03:17:31  Show Profile  Reply with Quote
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
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 10/27/2010 :  06:37:16  Show Profile  Reply with Quote
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

Edited by - elwoos on 10/27/2010 06:38:45
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000