Convert data time to minutes 1

[sisbril]

I have data in varchar format that could show date time or only time in some cases.

FIELD
2013-12-06 01:30:40
02:00:20


I need to convert these data to get minutes, so the result of the sum should be 211 minutes.

thank you.

 

[feherke]

Hi

Until we wait for a professional solution, this does the calculation too :

[b]create[/b] [b]table[/b] #temp (
  field [b]varchar[/b](100)
)

[b]insert[/b] [b]into[/b] #temp [b]values[/b]
( [green][i]'2013-12-06 01:30:40'[/i][/green] ),
( [green][i]'02:00:20'[/i][/green] )

[b]select[/b]
cast(round(sum(datepart(hh, cast(field [b]as[/b] [b]time[/b])) * 60 + datepart(mi, cast(field [b]as[/b] [b]time[/b])) + datepart(ss, cast(field [b]as[/b] [b]time[/b])) / 60.0), 0) [b]as[/b] [b]int[/b])

[b]from[/b] #temp

field
-----------
211

Feherke.
feherke.ga

 

[gmmastros]

Declare @Temp Table(Field VarChar(20))

Insert Into @Temp Values('2013-12-06 01:30:40')
Insert Into @Temp Values('02:00:20')
Insert Into @Temp Values('Fast')

Select	Sum(DateDiff(Second, 0, Case When IsDate(Field) = 1 Then Convert(Time, Field) End) % 86400 / 60.0)
From	@Temp

The previous code assumes you are using SQL2008 or newer because it uses the time data type.

If you are using a version of SQL Server that does not support the TIME data type, then you could use this method which basically does the same thing.

Declare @Temp Table(Field VarChar(20))

Insert Into @Temp Values('2013-12-06 01:30:40')
Insert Into @Temp Values('02:00:20')
Insert Into @Temp Values('Fast')

Select	Sum(DateDiff(Second, 0, Case When IsDate(Field) = 1 Then Convert(DateTime, Field) - Convert(Int, Convert(DateTime, Field)) End) % 86400 / 60.0)
From	@Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom