DAYS 360 Function

Hi,

Does anyone has the DAYS360 excel formula in a function in sqlserver ? I did this one

FUNCTION dbo.fnDays360_EXCEL
(
@.startDate DateTime,
@.endDate DateTime
)
RETURNS int
AS
BEGIN
RETURN (
(CASE
WHEN Day(@.endDate)=31 THEN 30
ELSE Day(@.endDate)
END) -
(CASE
WHEN Day(@.startDate)=31 THEN 30
ELSE Day(@.startDate)
END)
+ ((DatePart(m, @.endDate) + (DatePart(yyyy, @.endDate) * 12))
-(DatePart(m, @.startDate) + (DatePart(yyyy, @.startDate) * 12))) * 30)
END

But there is a bug, if the end date is bigger then february, february must have 30 days and not 28 or 29...

Does anyone has the solution ?

Thanks

Hi,

Do you mean 30/360? HEre's some C# code that was tested thouroughly, you should be able to get the idea. If you meant Act/360 get back to me.

Good luck,

John

double YearFrac(DateTime dtStartDate, DateTime dtEndDate, int iDaycount)

{

/* According to Excel:

Basis Day count basis

0 or omitted US (NASD) 30/360

1 Actual/actual

2 Actual/360

3 Actual/365

4 European 30/360

7 Bus/252

*/

switch( iDaycount )

{

case 0: // 30/360 (ISDA)

{

int d1, m1, y1, d2, m2, y2;

d1 = dtStartDate.Day;

m1 = dtStartDate.Month;

y1 = dtStartDate.Year;

d2 = dtEndDate.Day;

m2 = dtEndDate.Month;

y2 = dtEndDate.Year;

// ISDA rules

if (d1 == 31) d1 = 30;

if (d2 == 31 && d1 == 30) d2 = 30;

return (360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1)) / 360e0;

}

|||

Use the following code...

Code Snippet

Create Function dbo.Days360

(

@.StartDate Datetime,

@.EndDate Datetime

)

Returns Int

as

Begin

Declare

@.d1 int, @.d2 int,

@.m1 int, @.m2 int,

@.y1 int, @.y2 int;

Select @.d1 = Day(@.StartDate), @.m1 = Month(@.StartDate), @.y1 = Year(@.StartDate),

@.d2 = Day(@.EndDate), @.m2 = Month(@.EndDate), @.y2 = Year(@.EndDate)

If (day(@.StartDate) = 1) And (month(@.StartDate) = 3)

Select @.d1 = 30

If (@.d2 = 31) And (@.d1 = 30)-- Then

Select @.d2 = 30

Return ((@.y2 - @.Y1) * 360) + ((@.m2 - @.m1) * 30) + (@.d2 - @.d1)

End

go

Select dbo.Days360('1/20/2007', '2/3/2007')

|||

Hi,

There is a problem to this case Select dbo.Days360('1/31/2007', '4/15/2007') it returns 74 instead of 75

Thanks

|||

Hi

There is a problem to this case Select dbo.Days360('1/31/2007', '4/15/2007') it returns 74 instead of 75

And for this one also..

Select dbo.Days360('2/28/2007', '3/31/2007') that must return 30

For the C# code...is just this last error

DateTime conversion from GST to EST

I am trying to find a formula to convert the GST to local time EST as well as account for daylight savings time.

We had a formula that was working until this year when the dates on DST changed. (Probably redundant but changed from the 1st Sunday/April thru last Sunday/Oct to 2nd Sunday/March thru 1st Sunday/Nov).

This is what we were doing before; ugly but it worked...

Date
date({PROBSUMMARY1.OPEN_TIME})

DayLite
if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

Time
time({PROBSUMMARY1>OPEN_TIME})-(({@.DayLite})*3600)

DateAdjATL
if Time({PROBSUMMARY1.OPEN_TIME})<=TimeValue(05,00,00)then
DateTimeValue({@.Date}-1,{@.Time}) else DateTimeValue ({@.Date},{@.Time})

We are currently hard coding the dates and we all know how inefficient that is... ANY HELP IS GREATLY APPRECIATED!!!Looks like something is missing...
DayLite
if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

if that is correct, you may need to change the months to

if Month({PROBSUMMARY1.OPEN_TIME}) in 3 to 11 then else 5

Hard to tell....

since you have an if, but no value if the if is true.

if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

for example, if Month({prob...}) in 4 to 10 then (What ?) else 5

datetime column formula

Hello all:

Using EM to add a column Date_Entered with data type dateTime, what is the syntax to default the date to current date when record is added and to ensure it does not update if the record is modified at some other time in the future. Is it also possible to exclude the time when the column is updated (instead of 4/18/2003 9:32:56 PM the colum would be derived as 4/18/2003)

Is there a publication with listing of all legal suntax used in SQL2000?

Thank youbol has the syntax.

I don't advise using e-m to update the schema.

The sql would be something like

alter table x add dte datetime not null default convert(varchar(8),getdate(),112)

The column is not updated - only defaulted on insert.

If you want it to be set to the current date on update you can do it in a trigger.

Datetimes always include a time - the above will set it to midnight. It is up to you the format in which you display it.

daterange problem

Hi all

I am using Crystal reports 8.5 and SQL Server

i am littlebit confused in writing formula giving you the scenario :

in one table i have datefield
need to check that date falls under the given date range

(ex: given date range :09/12/2005 - 02/11/2006

field consists : 13/12/2005

like above i need the count of the fields which falls under that date range

please help meuse formula something like this
if '2-jan-2006' > '1-jan-2006' or '2-jan-2006' < '31-jan-2006' then
1
else
0

then use summary function sum.

hope it helps|||Thanks its helps for me

Another problem is there with date range

when i selects a startdatetime from daterange,it should go back to one year and fixes to date and month (i.e May 31)

startdatetime -- 03/04/2006

date should goes to -- 05/31/2005

i used dateadd('yyyy',-1, startdatetime) it is decreasing one year but i am unable to fix the date to May 31

thank you very much|||if date is fixed to 31-may then u can use this formula

left(cstr('31-may-'& val(right(cstr(cdate('8-jan-2006')),4))-1),11)|||Thank you very much

you showed me away to do this

thanks a lot|||Hi

when i used your formula

getting the value like 2,005

Actually my aim to take this as daterange instead of actual date range

so if it contains "," unable to take it as daterange

pls help me|||I don't see anyproblem in that formula except its returning a string.

can you write how, u r using that formula|||Hi,

I am using what u r send formula.
But i get comma oerator in the year.

i am using like this
'31/1/'+ cstr(val(right(cstr(cdate('8/1//2006')),4))-1)

i am getting
31/1/2,005|||Hi,

I am using what u r send formula.
But i get comma oerator in the year.

i am using like this
'31/1/'+ cstr(val(right(cstr(cdate('8/1//2006')),4))-1)

i am getting
31/1/2,005

i removed one slash from between 1//2006

put as a formula
'31/1/'+ cstr(val(right(cstr(cdate('8/1/2006')),4))-1)
and got my output as

31/1/2005.00|||Hi

Thanks for your reply

In the given date range i am getting some customer ids from database

what i want is need to check whether those customer ids are existing or not in the perticular date range(i.e different date range from given date range)

if i am getting customer ids for this date range (07/01/2005 - 07/31/2005)

need to check the customer ids are existing in tthis date range(06/01/2005 - 06/30/2005)

could you pls help me|||iam developing the crystal report there was small problem iam facing

please help me

How to use parameter of main report into the subreport

example

@.startdatetime is a parameter in main report
ia want to use the same parmeter in subreport

Datediff formula on insert returning null value

I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration.

Thank you in advnace for any help on this!

INSERTrequest(emp_id,request_submit_date, request_start_date,request_end_date,request_duration,request_notes,time_off_id)Select@.emp_id,GETDATE(),@.request_start_date,@.request_end_date, 1 +DATEDIFF(day, @.request_start_date, @.request_end_date) - (selectcount(*)from WeekEndsAndHolidayswhere DayOfWeekDatebetween @.request_start_dateand @.request_end_date),@.request_notes,@.time_off_id

Either request_start_date or request_end_date is coming across as null. Are you sure that request_duration is the only null field?|||

Motley,

Thanks for the response. It took a while to get posted and I figured it out way before it got posted. I had worked on it for about an hour before I posted this and figure it out 5 minutes after I posted it. I didn't have one of my text boxes bound correctly.

DateDiff Formula

hiiiiiiiiii
how to make formula for to get minute (time) use datediff
thanks...
jebatSooooooooo,

You want the number of minutes between two DateTimes using the DateDiff function.
I assume you've looked at the help on DateDiff? ...it's all there...|||yeah, it's pretty straightforward. i did the following to calculate the difference in days, i think for minutes you just have to use "n" instead of "d":

formula = DateDiff("d", Date1, Date2)

DateDiff

Hi,
I am using this formula and I want to reset (Zero) the formula on change of group and grand total it. Can't work out how to do it! Probably something really simple.
DateDiff ("s",Previous ({Order.DateReceived}) ,{Order.DateReceived})
Many ThanksYou can have two formulas,

first one will be group wise total

second one will have the entire total (grand total)|||Crystal won't allow me to summarise this field based on the time calcuation! DOH!