SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


break down into hours, minutes and seconds


break down into hours, minutes and seconds

Author
Message
TAman
TAman
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 62
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


Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151808 Visits: 19455
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20139 Visits: 10042
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

TAman
TAman
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 62
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;


TAman
TAman
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 62
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?
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20139 Visits: 10042
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

WayneS
WayneS
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22510 Visits: 10658
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

TAman
TAman
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 62
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.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20139 Visits: 10042
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

TAman
TAman
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 62
OK Thanks! I understand exactly. This should work great! Thx so much!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search