CODEWITHSUNDEEP
sqldatetime
Matt Weick
Matt Weick

Reputation: 332

How to subtract minutes from datetime field in SQL

Trying to figure how to subtract minutes from a datetime field.

select 
   '1' as PPCONO, b.new_SalesrepId as PPREP1, 
   MAX(a.ActualEnd - c.TimeZoneBias) as PPDATE, 
   count(b.new_SalesrepId) as PPCOUNT 
from 
   ActivityPointerBase as a
join 
   SystemUserExtensionBase as b on b.SystemUserId = a.OwnerId
join 
   UserSettingsBase as c on c.SystemUserId = b.SystemUserId
where 
   b.new_SalesrepId <> '99999999' 
   and a.ActivityTypeCode = '4201' 
   and b.new_SalesrepId is not NULL 
   and a.StateCode = '1' 
   and CONVERT(varchar(8), a.ActualEnd, 112) = '20140627'
group by 
   b.new_SalesrepId, CONVERT(varchar(8), a.ActualEnd, 112)
order by 
   b.new_SalesrepId ASC;

From the code above, the part MAX(a.ActualEnd - c.TimeZoneBias) where a.ActualEnd is the datetime field being pulled in and c.TimeZoneBias is value in minutes I am trying to subtract. Every time I run it, it subtracts days and not minutes. In this case c.TimeZoneBias actual value is '300' so it's subtracting 300 days and not 300 minutes.

Upvotes: 1

Views: 5953

Answers (2)

shree.pat18
shree.pat18

Reputation: 21757

Use DATEADD to subtract a specific number of a specific interval, like so:

DATEADD(minute,  (-1 * c.TimeZoneBias), a.ActualEnd )

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270653

I find the easiest way is to use arithmetic:

MAX(a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float)) as PPDATE

This converts the TimeZoneBias into fractions of a day.

Upvotes: 4

Related Questions