## 7 replies

Last post Aug 02, 2008 03:28 AM by bhappy

• bhappy

Member

8 Points

220 Posts

### How to subtract 1 hour from getdate()?

Hi All,

Can any one tell me how to subtract 1 hour from Getdate()? I tried like this

for example modify_date=10:55:55 am  and getdate=11:05:05 am

DATEDIFF(hh,MODIFY_DATE,GETDATE()) is giving result as 1 but i want difference 1 hr when getdate is 11:55:55 am ???

Any help is appriciated......

Thanks.

SQLServer

• johram

All-Star

16090 Points

3549 Posts

### Re: How to subtract 1 hour from getdate()?

Strange. It seems hh returns the difference in hour even though not 60 minutes have passed.

What if you try to determine the difference in minutes, mi, and check if it is greater than 60?

If this post was useful to you, please mark it as answer. Thank you!
• ramireddyind...

All-Star

22502 Points

4547 Posts

### Re: How to subtract 1 hour from getdate()?

Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
• Prashant Kum...

Star

8561 Points

2044 Posts

### Re: How to subtract 1 hour from getdate()?

Aug 02, 2008 02:38 AM|Prashant Kumar|LINK

#### johram

Strange. It seems hh returns the difference in hour even though not 60 minutes have passed.

There is nothing strange here.

#### MSDN

DATEDIFF (Transact-SQL)

Returns the number of date and time boundaries crossed between two specified dates.

You could use DATEDIFF (mi, date1, date2)/60 to get the difference in hours
PKS
• bhappy

Member

8 Points

220 Posts

### Re: How to subtract 1 hour from getdate()?

Hi,

select dateadd(hh,-1,getdate())    is comparing whole date.but there may be a chance of my modify_date and current date is differing in seconds? I want result as 1 hr when    modify_date=10:55:55 am  and getdate >11:55:55 am and < 12:55:55pm is it possible?

• Prashant Kum...

Star

8561 Points

2044 Posts

### Re: How to subtract 1 hour from getdate()?

Aug 02, 2008 03:12 AM|Prashant Kumar|LINK

In that case find the difference in seconds and then convert it into hours by dividing by 3600

PKS
• ramireddyind...

All-Star

22502 Points

4547 Posts

### Re: How to subtract 1 hour from getdate()?

as said by in above  post you can do edither

select datediff(mi,@modfiy_date,getdate())/60

or

select datediff(ss,@modfiy_date,getdate())/3600

Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
• bhappy

Member

8 Points

220 Posts