Varchar to Int Conversion - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Varchar to Int Conversion

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2010-08-09 : 10:05:57

Hi,

I have a column which is of type varchar. It represents date and time values

e.g : 2010-06-21 07:10:44

I have a requirement wherein I need to convert the hh:mm into minutes and represent as Integer using cast function

like 2010-06-21 07:10:44 should come out as 420 minutes in Integer format.

Need guidance/help regarding using the conv functions

Thank you,
dim

Dp

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-09 : 11:06:24

what would be the right answer for 2010-06-21 07:40:44 ?

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-09 : 11:14:57

Sorry, I had just finished rounding things in intervals for something I was working on! Assuming you're not rounding

declare @val as varchar(30)
set @val = '2010-06-21 07:25:44 '
select datepart(hour,@val) *60

Jim

Everyday I learn something that somebody else already knew

dim
Yak Posting Veteran

57 Posts

Posted - 2010-08-09 : 11:17:07

Hi Jim,

what would be the right answer for 2010-06-21 07:40:44 ?.......the answer would be 460 minutes.

I think the below code works for the calculation :

SELECT
CAST( substring((COLUMN), 12, 2)AS INT) * 60 + CAST( substring((COLUMN), 15, 2)AS INT)
FROM
TABLE

The code will extract hours and convert it to minutes by multiplying by 60 and then adds the minutes part .

Thank you,
dp

Dp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-09 : 11:37:09

[code]select
[Minutes] = (datepart(hour,a.Dt)*60)+datepart(minute,a.Dt),
a.*
from
( -- Test data
select DT = convert(datetime,'2010-06-21 07:40:44')
) a[/code]
Results:
[code]Minutes DT
----------- -----------------------
460 2010-06-21 07:40:44.000[/code]

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-09 : 12:44:52

I was just goofing around, but thought I'd post anyway... If you have SQL 2008, you could also make use of DATEDIFF and a CAST to DATE (resusing MVJ's code):

select
	DATEDIFF(MINUTE,CAST(a.DT AS DATE), CAST(a.DT AS DATETIME)) AS [Minutes],
	a.*
from
	( -- Test data
	select DT = convert(datetime,'2010-06-21 07:40:44')
	) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources