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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ
 All Forums  General SQL Server Forums  New to SQL Server Programming  decimal to time Reply to Topic  Printer Friendly
Author  Topic

pnasz
Posting Yak Master

101 Posts

 Posted - 10/25/2010 :  09:40:26 how to convert decimal to time ex8.30 to 8:30

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2052 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
Posting Yak Master

101 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
843 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 TTo walk FAST walk ALONE To walk FAR walk TOGETHER

pnasz
Posting Yak Master

101 Posts

 Posted - 10/25/2010 :  10:13:12 8.30 is decimal number righti want to convert it to time format like 08:30 u getting meone table is havingempid timein1 8.32 8.45i want to select this data and put in to another tablesay attendanceid timein timeout 1 08:30 2 08:45

vaibhavktiwari83
Aged Yak Warrior

India
843 Posts

 Posted - 10/25/2010 :  10:20:34 quote:Originally posted by pnaszits decimal no that id number like 8.33 i want to show it as 08:30 in time format using sql serverI 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 TTo walk FAST walk ALONE To walk FAR walk TOGETHER

Lamprey
Flowing Fount of Yak Knowledge

4614 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.59SELECT cast(replace(cast(@x as varchar(5)),'.',':') as time);Best regards,Devart,SQL Server Tools:dbForge Schema ComparedbForge Data ComparedbForge Query Builder

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 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"

Premature Yak Congratulator

India
22864 Posts

 Posted - 10/26/2010 :  10:35:06 DECLARE @Sample SMALLMONEY = 8.3select PARSENAME(@sample,2)+':'+PARSENAME(@sample,1)MadhivananFailing to plan is Planning to fail

Premature Yak Congratulator

India
22864 Posts

 Posted - 10/26/2010 :  10:36:35 orselect REPLACE(@sample,'.',':')MadhivananFailing to plan is Planning to fail

Premature Yak Congratulator

India
22864 Posts

 Posted - 10/26/2010 :  10:41:50 quote:Originally posted by pnasz8.30 is decimal number righti want to convert it to time format like 08:30 u getting meone table is havingempid timein1 8.32 8.45i want to select this data and put in to another tablesay attendanceid timein timeout 1 08:30 2 08:45What do you want for these?7:778:92MadhivananFailing to plan is Planning to fail

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2052 Posts

 Posted - 10/26/2010 :  11:32:14 quote:Originally posted by pnaszits decimal no that id number like 8.33 i want to show it as 08:30 in time format using sql serverAren'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
843 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 TTo walk FAST walk ALONE To walk FAR walk TOGETHER

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2052 Posts

 Posted - 10/27/2010 :  06:37:16 quote:Originally posted by vaibhavktiwari83I 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
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC