Click here to Skip to main content
15,903,724 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Im trying to subtract a specific time (eg. 1 hour 15 mins) from a time (eg. 12:00:00).

Currently i have tried

(TIMESTART - '01:30:00')
(TIMESTART - 013000)

Any help would be greatly appreciated. Thanks in advance
Posted
Comments
What have you tried and where is the issue?

Use DATEADD() and DATEPART(), read this: dateadd()[^] and
datepart()[^]
Let me give you one example, say one hour ago:
SELECT DATEPART(hour, DATEADD(hour, -1, GETDATE()))

Try to work out the solution yourself.
 
Share this answer
 
v3
Comments
nebiam 11-Jan-14 2:15am    
Im trying to work it into a case statement but it doesnt like it...

CASE
WHEN example = 'A' THEN DATEADD(hour, -1, TIMESTART)
ELSE TIMESTART
END AS STARTTIME
nebiam 11-Jan-14 2:16am    
where TIMESTART eg. '12:00:00'
You said this does not work:

VB
CASE
WHEN example = 'A' THEN DATEADD(hour, -1, TIMESTART)
    ELSE TIMESTART
    END AS STARTTIME


Well, dateadd works with a date, and returns a date. TIMESTART appears to be a string which represents a time.

try

VB
CASE
WHEN example = 'A' THEN convert(time, DATEADD(hour, -1, TIMESTART))
    ELSE convert(time, TIMESTART)
    END AS STARTTIME


case requires everything you return has the same type.
 
Share this answer
 
This is the solution that worked in the end. Thanks for all the suggestions though. In DB2 you can actually add/subtract time like this.

CASE
WHEN example = 'A' THEN TIMESTART - 30 MINUTES
ELSE TIMESTART
END AS STARTTIME
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900