Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

break down into hours, minutes and seconds Expand / Collapse
Author
Message
Posted Wednesday, July 29, 2009 10:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 3, 2012 1:19 PM
Points: 18, Visits: 54
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

Post #761744
Posted Wednesday, July 29, 2009 10:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:56 PM
Points: 31,168, Visits: 15,612
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
Post #761763
Posted Wednesday, July 29, 2009 10:26 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #761766
Posted Wednesday, July 29, 2009 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 3, 2012 1:19 PM
Points: 18, Visits: 54
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;

Post #761836
Posted Wednesday, July 29, 2009 12:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 3, 2012 1:19 PM
Points: 18, Visits: 54
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?

Post #761892
Posted Wednesday, July 29, 2009 1:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #761918
Posted Wednesday, July 29, 2009 1:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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
Post #761923
Posted Wednesday, July 29, 2009 1:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 3, 2012 1:19 PM
Points: 18, Visits: 54
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.
Post #761930
Posted Wednesday, July 29, 2009 1:40 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #761935
Posted Wednesday, July 29, 2009 1:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 3, 2012 1:19 PM
Points: 18, Visits: 54
OK Thanks! I understand exactly. This should work great! Thx so much!!!

Post #761945
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse