Convert Decimal 6,0 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

SQL Server 2008 Forums

Transact-SQL (2008)

Convert Decimal 6,0 to Time

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2011-02-23 : 10:59:13

I have a time field thats called

time1 it's a Decimal 6,0 it is currently in an hhmmss format just not a "Time" constraint What is the best way to convert it to hh:mm or hh:mm:ss

it is on a 24 hour clock and in the early mornings the leading 0's do not come across for instnace 46 is 00:00:46

94046 = 09:40:46

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-23 : 11:48:24

[code]
DECLARE @org decimal(6) = 94046
SELECT CAST(DATEADD(second, FLOOR(@org / 10000) * 3600 + FLOOR(@org / 100) % 100 * 60 + @org % 100, 0) as time)
[/code]

ZMike
Posting Yak Master

110 Posts

Posted - 2011-02-23 : 23:31:41

IFor,

When I leave "Time" in sql server it doesnt like it and says it's not a system type

Msg 243, Level 16, State 1, Line 14
Type TIME is not a defined system type.

However if I put Datetime in it seems to work. I guess I'll just have to substring the data out because this is what that returns 1900-01-01 09:40:46.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 23:59:01

are you using SQL Server 2008 ? time data type is only available from SQL 2008 onwards

KH
[spoiler]Time is always against us[/spoiler]

ZMike
Posting Yak Master

110 Posts

Posted - 2011-02-24 : 00:05:54

khtan,

I thought it was a 2008 server. I'll have to double check tomorrow. On my machine I'm running R2, but I'll have to follow up on the server. Maybe the one I'm calling is a 2005.. That would make sense why the other methods that I had tried didnt work.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 00:06:42

Check if this works for you

declare @x varchar(10)
set @x='94046'
If LEN(@x)=5
set @x='0'+@x
select reverse(SUBSTRING(REVERSE(@x),1,2)+':'+SUBSTRING(REVERSE(@x),3,2)+':'+SUBSTRING(REVERSE(@x),5,2))

cheers
MIK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-24 : 00:13:52

if you want the time to return as string


select	stuff(stuff(right('000000' + convert(varchar(6), time1), 6), 3, 0, ':'), 6, 0, ':')

KH
[spoiler]Time is always against us[/spoiler]

ZMike
Posting Yak Master

110 Posts

Posted - 2011-02-24 : 09:49:42

Khtan,

I am able to get it back as a sting. I used the following

CONVERT(VARCHAR (8),CAST(DATEADD(SECOND, FLOOR(time1/ 10000) * 3600 + FLOOR(time1 / 100) % 100 * 60 + time1% 100, 0) AS DATETIME) ,108)

Which actually does bring me to another question since I cant do any calculations off the time as a string.

I do like the logic that Ifor gave which brings me to another question.

if I use his logic I get the

1900-01-01 14:53:13.000 - bascially the 1900-01-01

if I have another field that is Datetime and shows

Field is called Date1

2011-02-23 00:00:00.000

What is the best way for me to combine the 2 so I get a true Datetime field

So if combined it would show 2011-02-23 14:53:13.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-24 : 09:56:18

if the time is already in string, then you can just use ADD to do it


select Date1 + stuff(stuff(right('000000' + convert(varchar(6), time1), 6), 3, 0, ':'), 6, 0, ':')

KH
[spoiler]Time is always against us[/spoiler]

ZMike
Posting Yak Master

110 Posts

Posted - 2011-02-24 : 10:07:25

Khtan,

Thanks ! I figured it out about the same time as your post

CONVERT(DATETIME,CONVERT(VARCHAR (12), date1) + ' ' + CONVERT(VARCHAR (8),(CAST(DATEADD(SECOND, FLOOR(time1/ 10000) * 3600 + FLOOR(time1 / 100) % 100 * 60 + time1 % 100, 0) AS DATETIME)) ,108) , 108)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-24 : 10:17:55

on your query,
1. you don't have to convert date1 to string and then concatenate with the time string
2. the time string is a bit long . .. you are converting to datetime and then back to string concatenate and then convert back to datetime again :(

if you are using Ifor method, you might was well just use dateadd() to add to the date1

select	dateadd(hour, floor(time1 / 10000), dateadd(minute, floor(time1 / 100) % 100, dateadd(second, time1 % 100, date1)))

KH
[spoiler]Time is always against us[/spoiler]

ZMike
Posting Yak Master

110 Posts

Posted - 2011-02-24 : 10:28:11

khtan

Thanks again. I just changed the logic to that. That's MUCH easier to read ! Seems like that would probably be easier on the server too. I sometimes think things through the hard way lol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources