.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to subtract 1 hour from getdate()?

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :ASP.Net

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......


View Complete Post

More Related Resource Links

Break apart timespan/duration group by hour segments

I need to break apart a timespan/duration value. I need to calculate the availability of resources by hour of the day. I have a call for service at 0945 that last until 1030 unit = E52 I have a call for servce at 0930 that last until 1015 unit = E53 I have a call for service at 1030 that last unitl 1230 unit = E53 query to show the following % of availabilty for said unit by hour of day 0900 to 1000 available 75% of the time for E52 0900 to 1000 available 50% of the time for E53 1000 to 1100 available 50% of the time for E52 1000 to 1100 avaialble 25% of the time for E53 1100 to 1200 available 0% of time for E53 1200 to 1300 available 50% of the time for E53   all times are actual DTS with dates, hour, minutes, seconds and can transcend past midnight. So in essence I am trying to calculate the availablity of a fire engine in your neighborhood. How often is it our of service on a call by hour of day.   Thanks   Jim

bug: subtract value from double returns invalid value!

Dim b as double  = 0.8 Dim a as double = 1 - b a contains the value 0.1999999996 in stead of 0.2 ???????????????????

How to add/subtract list in asp.net

Hi, I've 2 lists as shown below: List<string> a1 = new List<string>(); List<string> b1 = new List<string>(); List<string> c1 = new List<string>(); I want to add list a1 & b1 & subtract the list c1 from their sum. Can any one tell me how to do it implicit functions of c# & with efficient way? Thanks!  

Report Parameter Values - Can I use getdate() or now() or any other function?

Hi all,      I have a report that utilizes a calendar function but I need to make it a subscription report. Is it possible to use getdate() or now() on the Report Parameter Values? I have a text box that says Enter a Date and that is where I want to have the default value to be run with the subscription. Please let  me know if possible. Thank you.

Last 24 hour data


i have this table


1	0	2010-09-22 10:54:52.000	1
1	0	2010-09-22 10:53:44.000	1
1	0	2010-09-22 10:52:36.000	1
1	0	2010-09-22 10:51:28.000	1
1	0	2010-09-22 10:50:20.000	1
1	0	2010-09-22 10:49:12.000	1
1	0	2010-09-22 10:48:04.000	1
1	0	2010-09-22 10:46:42.000	1
1	0	2010-09-22 10:45:34.000	1
1	0	2010-09-22 10:43:18.000	1
1	0	2010-09-22 10:42:09.000	1
1	0	2010-09-22 10:40:56.000	1
1	0	2010-09-22 10:39:48.000	1
1	0	2010-09-22 10:38:40.000	1
1	0	2010-09-20 14:53:58.000	1
1	0	2010-09-20 14:52:50.000	1
1	0	2010-09-20 14:51:42.000	1
1	0	2010-09-20 14:50:34.000	1
1	0	2010-09-20 14:49:26.000	1
1	0	2010-09-20 14:48:18.000	1
1	0	2010-09-20 14:47:10.000	1
1	0	2010-09-20 14:45:43.000	1
1	0	2010-09-20 14:44:35.000	1
1	0	2010-09-20 14:42:19.000	1

how can i fetch the last 24 hour data? (it should be only one record)


Get Average from interval(Time) by hour


You Got to love management,

I've been averaging for the last 12 records with this code:

SELECT AVG(LMP) AS Average FROM (SELECT TOP (12) LMP FROM dbo.LMP_5Min order by id desc)

I need to get the average for just the current hours data that comes in every 5 minutes.  So there will be 12 records by the end of the hour.  However I need to average each increment as it comes in.  So after 1, 2, 3...... increments come in each would have to be averaged.

My tables goes as follows:


ID                  int, not null

Day         &n

Cluster service takes 1 hour to bring service and applications online


We had a 1 hour outage with our production SQL cluster recently and I have been trawling through the Event Logs to try and find an explanation.  I know that a network failure actually triggered the outage, during a switch upgrade no network was available and so the cluster shut down:

"This cluster node has no network connectivity. It cannot participate in the cluster until connectivity is restored. "

There are lots of enties telling me that the cluster service is stopping and one saying that DTC is terminating unexpectedly which is fine, but then I see a log enty:

"The system failed to flush data to the transaction log. Corruption may occur"

This is worrying - how can I be sure that my data is not corrupted?

I can see the SQL Server and SQL Server Agent services '"terminate unexpectedly" and shortly after that I can see the cluster service entering the running state and various attempts to bring clustered applications online.  These attempts give the following messsage:

"The Cluster service failed to bring clustered service or application 'SQL Server (INSTANCENAME)' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application."

Does this mean that the cluster service does no

Reading xlsx when cells are in hour format



I have a page that does an upload of an xlsx file to a datatable and write into SQL.

That is working, my problem is when i upload the file and pass the values to the datatable some of them are showing in the wrong format and the column name dont appears.


Hours at home

Hours working



01-01-1900 1:35:00

01-01-1900 2:35:00

01-01-1900 3:35:00

The first 2 columns its how i want the info the third is how my problem looks like.

I've tested and the problem occurs when the same column has more than one value.

The format of the cells is h:mm if i change it to text it works fine but i dont want that because the time will be converted to semething like this 0,123456789.

I dont know if it matters but the xlsx file is kinda complex and has 10,2MB.

Any pointers? Thanks for any reply

Get Average by hour but but not by top of hour


I got this code with the help of K H Tan:

declare @now datetime

select @now = dateadd(hh,0,getdate())

; with cte as

 (select LMP,

  tm = convert(datetime, left(interval, 19))

 from LMP_5Min


select avg(LMP)

from cte

where tm >= dateadd(hour, datediff(Hour, 0, @now), 0)

and tm < dateadd(hour, datediff(hour, 0, @now) + 1, 0)

My only problem is that it grabs the data at the top of the hour if it is 8:00 P.M. it starts at 8:00 and goes to the last 5 minute increment at 8:55. 

SSRS 2008 Time min change to hour


Hi there

I've got a problem in my report.


I have 2 colums "Expense" "Entity"


Expense has an issue 300 and entity min.

what i'd like is, that it change to 5 hours.

but it should issue min if expense is below an hour.


Any ideas?

One who can read, have a clear advantage!

How to Convert datetime to hour


int s = 0;

        //Start time
        int start_year =2010;
        int start_month = 10;
        int start_day = 2;
        int start_hour = 0;
        int start_min = 0;

        //End time
        int end_year = 2010;
        int end_month = 10;
        int end_day = 3;
        int end_hour = 0;
        int end_min = 0;
        DateTime startTime = new DateTime(start_year,start_month,start_day, start_hour, start_min,s); // 05:40 Hour
        DateTime endTime = new DateTime(end_year, end_month, end_day, end_hour, end_min, s); // 17:45 Hour
        TimeSpan diff = endTime - startTime;
        int hourDiff = diff.Hours; // 12 Hours
        int minDiff = diff.Minutes; // 15 Mins
        double totDiffHours = (double)hourDiff + (double)minDiff /60; // 12.25 Hours
       // double hhh = (DateTime) diff / 24;

        Label1.Text = Convert.ToString(diff);

How to convert days into hours?


Default Values in ADO Entity Framework (GetDate())


I have a sql server table with a date column that has a default value of GetDate()

this default does not seem to get passed to the EF and so i was wondering what the most elegant way was to code this default value within my asp.net application? I dont want to manually alter the EF Model as i may want to re-import it and dont want to lose these changes.

any suggestions gratefully appreciated



how to subtract value at previous row from current row


column1   Column2

1                1

2                1

10              8

20             10


Thanks & Regards, PSWASULE

GETDATE( ) : 3 months back


I need to add two parameters to my report... Start Date and End Date, the End Date will todays date which I can get from GETDATE() but how do i get the Start Date, which should be 3 months back from todays date?




Minus 1 hour in all columns in MySql



I have a timestamp column which I require to change the time back by 1 hour for all records.

Please post the SQL for this.


Winforms TimeSpan Subtract Method


I am capturing the date time in this format 19/10/2010 19:13:07 and store this in a lable. I want to covert this to actual time from start to finish. My codes does not work. Are there any simple ways to work this out?

  // Calculate start and finish time
            TimeSpan myTimeSpan11 = Timelabel.Text;
            TimeSpan myTimeSpam22 = Timelabel2.Text;
            TimeSpan myTimeSpan33 = myTimeSpan11.Subtract(myTimeSpan22);
            TickCounterlabel.Text = ("Time taken = " + myTimeSpan33.Duration());



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend