need to join integers together into a string

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 2000 Forums

Transact-SQL (2000)

need to join integers together into a string

Author

Topic

raritan
Starting Member

39 Posts

Posted - 2007-08-08 : 16:02:28

I have a database table of call records with the following columns:

call_hour_value - hour the call took place (not shown in military time, 0-12 only)
call_minute_value - minute the call took place
call_second_value - second the call took place
call_of_day_value - time of day the call took place, either AM or PM

Each of these is an integer data type. So if a call took place at 12:00:14 AM, the following values are in the columns:

call_hour_value - 12
call_minute_value - 0
call_second_value - 14
call_of_day_value - AM

In order to get this data into a format I can compare to another set of data, I need to first take the hour column and convert it to the hour in military format, where 12 AM is 0, 1 PM is 13, etc.

Then I need to take the hour, minute and second columns, and string them together into a string that will be saved as a varchar data type.

I also need the numbers to display in the string with two digits, even if the number is less than ten, I want nine to show up as 09, etc.

So, using my above example again , I would need 12:00:14 AM to be shown in this new column as : 000014

Any help would be appreciated, thank you

Kevin

raritan
Starting Member

39 Posts

Posted - 2007-08-08 : 16:04:28

Also, in testing this with a select statement on my table first, here is what I have so far, probably a better way of doing this I'm sure. It works on the minute and seconds, but it is not adding a leading zero to the hour value if it is less than 10:

select
call_hour_value,
call_minute_value,
call_second_value,
call_of_day_value,
str(
case when ltrim(call_hour_value) < 10 then
('0' + (case when rtrim(call_of_day_value) = 'AM' then
case when ltrim(call_hour_value) = 12 then (ltrim(call_hour_value) - 12) else (ltrim(call_hour_value)) end
else
case when ltrim(call_hour_value) = 12 then ltrim(call_hour_value) else (ltrim(call_hour_value + 12)) end
end))
else
(case when rtrim(call_of_day_value) = 'AM' then
case when ltrim(call_hour_value) = 12 then (ltrim(call_hour_value) - 12) else (ltrim(call_hour_value)) end
else
case when ltrim(call_hour_value) = 12 then ltrim(call_hour_value) else (ltrim(call_hour_value + 12)) end
end)
end
)
+
case when call_minute_value < 10 then ('0' + ltrim(str(call_minute_value))) else ltrim(str(call_minute_value)) end
+
case when call_second_value < 10 then ('0' + ltrim(str(call_second_value))) else ltrim(str(call_second_value)) end
as 'call_time'
from call_records

Can anyone help with this?
Thanks,
Kevin

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-08 : 16:18:38

to add leading 0 try this:

RIGHT('00' + Convert(Varchar,@hourValue),2)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-08 : 16:38:42

It would be much easier to format the date to the format you want and to compare times if you started by converting the individual columns to a date time value.

Of course, it would also be best to store these values in a datetime column to begin with.


select
	[Time] =
	dateadd(hh,case when call_of_day_value = 'PM' then 12 else 0 end+call_hour_value,
	dateadd(mi,call_minute_value,dateadd(ss,call_second_value,0)))
from
	(
	select
		call_hour_value = 11,
		call_minute_value = 45,
		call_second_value = 55,
		call_of_day_value = 'PM'
	) a



Results:
Time                                                   
------------------------------------------------------ 
1900-01-01 23:45:55.000

(1 row(s) affected)

CODO ERGO SUM

raritan
Starting Member

39 Posts

Posted - 2007-08-08 : 17:58:18

RIGHT('00' + Convert(Varchar,@hourValue),2) worked for me, thanks Dinakar. The other way, converting into a datetime data type, I don't believe would help me in my situation, because the source data that I'm comparing to has the call time shown as a varchar, not in any time format, and i want to be able to bring the to side by side into Excel, and compare those different fields, including the time field, record by record, and for the formatting, they need to match exactly.

Thank you both for the help
Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources