How to add varchar values in SQL

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

Transact-SQL (2008)

How to add varchar values in SQL

Author

Topic

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-07-15 : 07:31:45

How to add varchar values in SQL.Consider as both are time values.but the data type[both] is varchar and stroed in table

For example:

Column1 Column2
2.40:10 PM 5:22:00

i need the result for the following format
8.02[8 hours and 2 minutes]

V.NAGARAJAN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 07:32:39

Why are you using VARCHAR to store times?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 07:35:34

[code]DECLARE @Sample TABLE
(
Column1 VARCHAR(11),
Column2 VARCHAR(11)
)

INSERT @Sample
SELECT '2:40:10 PM', '5:22:00'

SELECT *
FROM @Sample

SELECT CONVERT(CHAR(8), CAST(REPLACE(Column1, 'PM', '') AS DATETIME) + CAST(REPLACE(Column2, 'PM', '') AS DATETIME), 114)
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-07-15 : 07:36:25

That is client Requirement.i know the solution in oracle.so i want SQL Server.In Oracle, this is the query

select interval '2:40:10' hour to second + interval '5:22:00' hour to second from dual

V.NAGARAJAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 07:37:32

[code]SELECT
STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes + theSeconds, '19000101'), 8), 1, 2, CAST((theHours + theMinutes + theSeconds) / 3600 AS VARCHAR(12)))
FROM (
SELECT ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 3600 * LEFT(data, CHARINDEX(':', data) - 1) END)) AS theHours,
ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 60 * SUBSTRING(data, CHARINDEX(':', data) + 1, 2) END)) AS theMinutes,
ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE right(data,2) END)) AS theSeconds
-- put your data here or make a table instead
FROM (select '2:40:10' as data union all
select '5:22:00')dt
) AS d
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-07-15 : 07:40:31

Ok Good It's working fine.But i want the result fir the following format

08.02

So how to convert that

V.NAGARAJAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 07:51:18

[code]
DECLARE @Sample TABLE
(
Column1 VARCHAR(11),
Column2 VARCHAR(11)
)

INSERT @Sample
SELECT '2.40:10 PM', '5:22:00'

SELECT *
FROM @Sample

SELECT CONVERT(CHAR(8), CAST(REPLACE(REPLACE(Column1, 'PM', ''),'.',':') AS DATETIME) + CAST(REPLACE(Column2, 'PM', '') AS DATETIME), 114)
FROM @Sample

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-07-15 : 07:52:32

Hello Peso,
It's working fine.but i want result, the following format
08.02
So how to convert it.pls

V.NAGARAJAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 07:57:24

[code]DECLARE @Sample TABLE
(
Column1 VARCHAR(11),
Column2 VARCHAR(11)
)

INSERT @Sample
SELECT '2.40:10 PM', '5:22:00'

SELECT *
FROM @Sample

SELECT left(replace(CONVERT(CHAR(8), CAST(REPLACE(REPLACE(Column1, 'PM', ''),'.',':') AS DATETIME) + CAST(REPLACE(Column2, 'PM', '') AS DATETIME), 114),':','.'),5)
FROM @Sample
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-07-15 : 08:00:51

Ok Goog and its Working fine.Thanks.

V.NAGARAJAN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 08:06:43

[code]SELECT REPLACE(CONVERT(CHAR(5), CAST(REPLACE(REPLACE(Column1, 'PM', ''),'.',':') AS DATETIME) + CAST(REPLACE(Column2, 'PM', '') AS DATETIME), 114), ':', '.')
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources