DateTime Addition Problem

Hi All,

My problem is I have a field called 'Production hours' in the report which is a decimal field. I need to add it to 'Scheduled Date' which is a date time field. I was using the DateAdd function in the formula. But if I have values like 0.50 for Production-hours, then the function does not work.
THe formula is as follows:

datetimevar x;
if not isnull({dpRptScheduleReport.prod_hrs}) then
(
x:=DateAdd('h', {dpRptScheduleReport.prod_hrs},{@.Schd_Start_Time});
if DayOfWeek(x)=7 then x:=DateAdd("d",2,x) else
if DayOfWeek(x)=1 then x:=DateAdd("d",1,x);
x;
)
else
{@.Schd_Start_Time};

Here dpRptScheduleReport is the Stored Proc. Can anybody help me please??

Thanks
Rashmi.Hi Rashmi

Try using this.

DateAdd('n',(.50*60) ,datetime('1-jan-2006'))

x:=DateAdd('n', {dpRptScheduleReport.prod_hrs}*60,{@.Schd_Start_Time});

Hope it helps..|||Hey thanks Raheem...It worked !!!!!!!

Rashmi

DateDiff - Return Months in Decimal?

I've been trying to create a function that returns the difference, in months, between two dates. The DateDiff function (m) returns an integer, but I really need a decimal. I could return the days instead, but it would be difficult to figure out how the number of months from this, especially when the dates span multiple years and I need to adjust for leap year. Does anyone know of a resource that might have a solution for this?

Thanks,
RobI guess you're looking for the decimal because you want what percentage of the month?

Do you need to know the number of days in that month?

It's not really a straight forward kind of thing.

I mean is this really 2 months?

SELECT DATEDIFF(m,'6/30/2004','8/31/2004')

Or this one being 1?

SELECT DATEDIFF(m,'6/30/2004','7/1/2004')|||A value of 1.5 days has meaning since it always comes out to 36 hours or x minutes or whatever. A value of 1.5 months has no meaning since months are not all the same length. 1.5 Februarys has less days than 1.5 Marchs.

Or this one being 1?

SELECT DATEDIFF(m,'6/30/2004','7/1/2004')

Seems like it if I'm at work... :)|||You could do years with a decimal...

check out the bio

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=7198|||.695890

Cool... I'll use that formula next time I have a government form to fill out. Drive them nuts for a change.

Only problem is that years don't have 365 days in them. Actually around 365.25 I think. Hence the 'makeup' day in leap years. Since time is a human concept, why not just make every month 30 days. Year end would see us 4 days short but Dec 31st we could make 120 hours long vs. 24. That would keep the calendar in sync with the seasons. Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover... :)|||.695890

Cool... I'll use that formula next time I have a government form to fill out. Drive them nuts for a change.

Only problem is that years don't have 365 days in them. Actually around 365.25 I think. Hence the 'makeup' day in leap years. Since time is a human concept, why not just make every month 30 days. Year end would see us 4 days short but Dec 31st we could make 120 hours long vs. 24. That would keep the calendar in sync with the seasons. Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover... :)

That's funny....|||Actually, the solar year is pretty close to 365.2425 terrestrial days. So we just... Oh nevermind, I've been down this road WAY too many times now!

-PatP|||Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover... :)You can recover from a hangover ?!?! We just figured it would never end, so we never allow the hangover to start!

-PatP|||A pre-req for most DBA positions for sure. Listening to end users gripe for 8 hours a day used to be painful. Since I took up drinking I can now blissfully tune them out. Boss came to me years ago and said he felt my drinking was interferring with my work. I replied that my work was interferring with my drinking and quit on the spot. Never looked back. Next round's on me!