| Author |
 Topic  |
|
|
pnasz
Yak Posting Veteran
89 Posts |
 Posted - 10/25/2010 : 09:40:26
|
how to convert decimal to time
ex
8.30 to 8:30 |
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2039 Posts |
Posted - 10/25/2010 : 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
Yak Posting Veteran
89 Posts |
Posted - 10/25/2010 : 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
India
837 Posts |
Posted - 10/25/2010 : 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
Yak Posting Veteran
89 Posts |
Posted - 10/25/2010 : 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
India
837 Posts |
Posted - 10/25/2010 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 10/25/2010 : 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 - 10/26/2010 : 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
Sweden
29138 Posts |
Posted - 10/26/2010 : 03:26:31
|
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" |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 10/26/2010 : 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
India
22461 Posts |
Posted - 10/26/2010 : 10:36:35
|
or
select REPLACE(@sample,'.',':')
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 10/26/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
2039 Posts |
Posted - 10/26/2010 : 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
India
837 Posts |
Posted - 10/27/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
2039 Posts |
Posted - 10/27/2010 : 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 |
Edited by - elwoos on 10/27/2010 06:38:45 |
|
|
| |
Topic |
|
decimal to time
