Converting int to hh:mm:ss format 1

[juniordba]

Hi All,

Yup im a newbie to SQL and I've read on cast and convert before asking this question. But basically I have a column (int datatype) that is actually showing a time value. (i.e. 25755 - actually means 2:57:55 in hours:minutes:seconds).

Now what I want to do is to do a select on this column to change it to the following format -- hh:mm:ss.

Tips please? Thanks in advance.

 

[pankajv]

Your question is ambiguos. What will be following considered as:
11111 -> 11:11:01 or 01:11:11 or 11:01:11
1111 -> 01:01:11 or 11:01:01 or 01:11:01

Please provide the basic rules to interpret the value.

 

[tlbroadbent]

Try this query using the REPLACE, STR, and STUFF functions.

Select TimeValue = replace(stuff(stuff(str(IntColumn,6),5,0,':'),3,0,':'),' ',':')
From YourTable
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[pankajv]

Terry,
Your query assumes that there are always 6 digits in the table, but from the example, which juniordba has given it seems that he can have a 5 digit number also in the table.

 

[tlbroadbent]

True. But the query right justifies and zero fills the missing digits.

253 --> 00:02:53
5 --> 00:00:05
12345 --> 01:23:45 If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[pankajv]

No it does not:
Following are results of the above examples:
253 -> ::::2:53
5 -> :::::::5
12345 -> :1:23:45

 

[tlbroadbent]

Sorry, I made an error when posting.

Select TimeValue = replace(stuff(stuff(str(IntColumn,6),5,0,':'),3,0,':'),' ','0')
From YourTable

Now it does as I indicated. Thanks for pointing out my error. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[juniordba]

awesome! it works..thanks all for the help =)

 

[tlbroadbent]

Which solution worked for you? If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Kurniadi]

Hi, juniordba...
The important, please use STUFF Function.

 

[Kurniadi]

Hi...

Or may be you can try like this...

Select TimeValue = Substring(str(IntColumn,6),1,2)+
Substring(stuff(str(IntColumn,6),1,2,':'),1,3)+
Substring(stuff(str(IntColumn,6),1,4,':'),1,3)

don't forget to use STR(IntColumn, 6) ! -> hh:mm:ss

 

[juniordba]

This solution worked:

Select TimeValue = replace(stuff(stuff(str(IntColumn,6),5,0,':'),3,0,':'),' ','0')
From YourTable

But im also going to try the last one...will let you know how it goes..

once again, thanks for the help!