July 29, 2009 at 10:02 am
I have the following data in my table. The Act_Time is (bigint) and is not broken down into hours, minutes and seconds. I need to break down into time. example: 80024 would need to be 8:00:24 and 191209 would need to be 19:12:09
Act_Time
80024
80026
80048
80051
191209
191251
191258
181139
161550
This is what I've put together so far. Not getting very far.. Need some HELP! with my code.
LEFT(Act_Time,2) + ':' +
CAST(SUBSTRING(Act_Time,4,2) AS Int)
END AS Time_Block
SELECT *
FROM tbl_Table
July 29, 2009 at 10:22 am
You'll need to use a CASE with LEN to determine what the places mean. Then the SUBSTRING to split out the items.
The issue is that if you have
1101
is that 1:10:01 or 11:00:10 or 1:1:01?
Once you figure out how the algorithm works, you can build a couple CASE statements to clean things up.
July 29, 2009 at 10:26 am
Assuming that your times are what you have shown, the following should work:
Use tempdb;
Go
If object_id('tempdb..#test') Is Not Null
Drop Table #test;
Go
Create Table #test (TimeValue bigint);
Go
Insert Into #test Values(000024);
Insert Into #test Values(000124);
Insert Into #test Values(001224);
Insert Into #test Values(012024);
Insert Into #test Values(180024);
Insert Into #test Values(080024);
Insert Into #test Values(080026);
Insert Into #test Values(080048);
Insert Into #test Values(080051);
Insert Into #test Values(191209);
Insert Into #test Values(191251);
Insert Into #test Values(191258);
Insert Into #test Values(181139);
Insert Into #test Values(161550);
Go
Select *
,stuff(stuff(right('00000' + cast(TimeValue As varchar(6)), 6), 5, 0, ':'), 3, 0, ':') As NewTimeValue
From #test;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2009 at 11:34 am
I'm not sure what you are representing when you say "stuff"("stuff"
And what does the '00000' represent?
Select *
,stuff(stuff(right('00000' + cast(TimeValue As varchar(6)), 6), 5, 0, ':'), 3, 0, ':') As NewTimeValue
From #test;
July 29, 2009 at 12:46 pm
Okay, I have created this code to convert the bigint into time.
SELECT *, CONVERT(BIGINT(6), ACT_TIME, 114) AS NEW_ACT_TIME
FROM dbo.tbl_table
I receive this error:
Msg 291, Level 16, State 1, Line 1
CAST or CONVERT: invalid attributes specified for type 'bigint'
I understand what I think it's saying... Since the length in my data is not always (6), I need to add CASE When (length) statments.... The only thing is, I don't know where to add this in my code. I have two lengths (see original data I sent). Can someone help me?
July 29, 2009 at 1:11 pm
TAman (7/29/2009)
I'm not sure what you are representing when you say "stuff"("stuff"And what does the '00000' represent?
Select *
,stuff(stuff(right('00000' + cast(TimeValue As varchar(6)), 6), 5, 0, ':'), 3, 0, ':') As NewTimeValue
From #test;
STUFF is a function to stuff characters into a string. We prepend 5 zeros to the string to make sure we will always have all 6 characters we need in the string. Then we stuff a colon into the appropriate spaces.
In the sample data I provided, the first value inserted into the table is 00:00:24 (000024). When inserted into a bigint column, the leading zeros will be removed.
If you take the code that I provided and ran it - you would see that it returns exactly what you are looking for.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2009 at 1:18 pm
TAman (7/29/2009)
I'm not sure what you are representing when you say "stuff"("stuff"And what does the '00000' represent?
Select *
,stuff(stuff(right('00000' + cast(TimeValue As varchar(6)), 6), 5, 0, ':'), 3, 0, ':') As NewTimeValue
From #test;
What the "00000" is doing is to make your string longer than necessary. You then take the right 6 values, making a "short" time (i.e. 80019) be the proper length (i.e. 080019).
The stuff function is fully documented in BOL at http://msdn.microsoft.com/en-us/library/aa259350(SQL.80).aspx. Basically, it is "stuffing" the colons into the time string. Each "stuff" call is putting one colon in; thus it takes two calls to get the time correct.
Okay, I have created this code to convert the bigint into time.
SELECT *, CONVERT(BIGINT(6), ACT_TIME, 114) AS NEW_ACT_TIME
FROM dbo.tbl_table
I receive this error:
Msg 291, Level 16, State 1, Line 1
CAST or CONVERT: invalid attributes specified for type 'bigint'
The bigint datatype does not support a size declaration; thus the "(6)" is invalid. This is what the error message is saying.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 29, 2009 at 1:29 pm
Okay, I understand how your code is working now. But your sample data is not like my sample data. My data doesn't always have 6 characters. It is sometimes 5 characters and 6 characters.
July 29, 2009 at 1:40 pm
TAman (7/29/2009)
Okay, I understand how your code is working now. But your sample data is not like my sample data. My data doesn't always have 6 characters. It is sometimes 5 characters and 6 characters.
I know your data does not always have 6 characters - neither does mine if you look at what is actually in the table. I inserted the data that way to show what happens for times with leading zeros when stored using bigint.
You will have times in your data with only a single digit - for times that are 00:00:00 through 00:00:09. Those times will be stored in the table as 0 through 9.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2009 at 1:59 pm
OK Thanks! I understand exactly. This should work great! Thx so much!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply