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 format

Hi.
I have two parameters called StartDate and EndDate.These parameters are from datetime type.
I want to view the records between these dates.I have some questions:

1)In the database, these parameters' values are like 15.11.1984 23:59:14. It has time value near the date value.But I don't want to view the time value.I only want the date part.

2)In the preview tab, I choose a date clicking the calendar image near the parameter textbox.
For example I choose 02.05.2001 and when I click the view report button, it changes to 05.02.2001.So there is a format difference.I want it to show like dd.mm.yyyy

3)By default, if the user doesn't enter a date, I want to view all the records.Any idea about this?

Thanks!

Try doing a convert on your database datetime field similar to this in your query

convert(datetime, "datefield", 104)

This will format the date as dd.mm.yyyy. You can also do this on the parameter value so they are both in the same format. Your query would look something like this:

select * from table where convert(datetime, "datefield", 104) >= convert(datetime, @.StartDate, 104) and convert(datetime, "datefield", 104) <= convert(datetime, @.EndDate,104)

To display all records you can set the default values to the maximum and minimum dates in your database. The issue with this is that everytime the report is opened, it will automatically run for all dates. Not sure how to make it work only if the user doesn't select dates.

|||kmcclung thanks for the reply.
But it didn't work.
I wrote convert(datetime, myDateField, 104) and then tried the third parameter for 103, 4, ...
But it didn't change.
Then I realized that it is not dependent on that number.
It uses only the default datetime format.
The records in my database are like dd.mm.yyyy hh:mm:ss
And after I used the CONVERT function NOTHING changed.
I only want the date part to be visible.(only want this)
And the second problem is that as I said before when I click the calendar button near the date texbox area and select a date like 15.12.2001 then it is written to textbox like 12.15.2001.
And because of not existing a month number like 15 an error occurs.
I mean that I want to change that calendar's format.

How can I correct this?|||What type of database you are using?|||

0) It sounds like your database is NOT storing dates with a DATETIME format. Why not?

1) To take '15.11.1984 23:59:14' and store it as a DATETIME with time stripped off (set to midnite):

CONVERT(DATETIME, CAST(CONVERT(DATETIME, '15.11.1984 23:59:14', 104) AS INT))

DATETIME

Below SQL works
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select top 10 * from mytable where mydate between @.startdate and @.enddate
But , below one error out with message
Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from mytable where mydate between ' + @.startdate +
' AND ' + @.enddate
execute @.sql
Here @.startdate and @.enddate are parameters and used inside a SP.
I have to use dynamic SQL for my logic and don't want to use CONVERT
function.
How to make this dynamic SQL work '
Thx
ShShamin,
the following code should work:
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from MyTable where MyTime between '''
+ cast(@.startdate as varchar) + ''' AND ''' + cast (@.enddate as varchar) +
''''
execute (@.sql)
hope this helps
Quentin
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#TgE1pwSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Below SQL works
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select top 10 * from mytable where mydate between @.startdate and
@.enddate
> But , below one error out with message
> Server: Msg 241, Level 16, State 1, Line 4
> Syntax error converting datetime from character string.
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select @.sql = 'select * from mytable where mydate between ' + @.startdate
+
> ' AND ' + @.enddate
> execute @.sql
> Here @.startdate and @.enddate are parameters and used inside a SP.
> I have to use dynamic SQL for my logic and don't want to use CONVERT
> function.
> How to make this dynamic SQL work '
> Thx
> Sh
>
>

datediff without weekends

How would I use the datediff function without counting wends?
declare @.StartDate SmallDateTime
declare @.EndDate SmallDateTime
set @.StartDate = '10/01/05'
set @.EndDate = getdate()
select datediff(d,@.StartDate,@.EndDate)
--except for the wends
This value returns 18, but I want it to not count wends so it should only
return 13.
--
TIA,
ChrisRChris,
Take a look at:
[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_threa
d/thread/fc6d9c7aa9b2580d/af9e9a9f851db285?lnk=st&q=Date+Difference+without+wen
ds&rnum=1&hl=en#af9e9a9f851db285[/url]
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:0217993F-F2D8-4B05-ADE9-06DC7D2957F6@.microsoft.com...
> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should
> only
> return 13.
> --
> TIA,
> ChrisR|||> set @.StartDate = '10/01/05'
Egads, this is a very ambiguous date format. I highly recommend using a
more standard format, e.g. YYYYMMDD, that cannot suddenly break if you
change your regional settings or run your code in a database with a
different language or dateformat setting...

> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
Use a calendar table, then you can incorporate holidays and other
non-working days too.
http://www.aspfaq.com/2519|||Look up how to design a Calendar table, which is a more general tool
for all of the temporal queries you will do.|||This link may help with Joe's suggestion.
http://www.aspfaq.com/show.asp?id=2519
HTH
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129763725.019473.177630@.o13g2000cwo.googlegroups.com...
> Look up how to design a Calendar table, which is a more general tool
> for all of the temporal queries you will do.
>|||Hey, Jerry, make the lazy bum work for it !! Google is a good habit
instead of the "do my homework for me" mentality that floods the
Newsgroups.|||Chris,
If you only want to exclude wends, here's one way to achieve this
(assuming Monday as the first day of the w):
SELECT
days/7*5 + days%7
- CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
- CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
FROM (SELECT
DATEDIFF(day, @.StartDate, @.EndDate) + 1 AS days,
DATEPART(wday, @.StartDate + @.@.DATEFIRST - 1) AS wd
) AS D;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:0217993F-F2D8-4B05-ADE9-06DC7D2957F6@.microsoft.com...
> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should
> only
> return 13.
> --
> TIA,
> ChrisR|||Thanks to all...
--
TIA,
ChrisR
"ChrisR" wrote:

> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should on
ly
> return 13.
> --
> TIA,
> ChrisR

dateadd in expression error

I have the following in a textbox expression:
=datepart("d",dateadd("d", 3, @.startDate))
I have this or something similar in many textboxes and they are all
returning the following error:
The value expression for the textbox â'textbox24â' contains an error:
[BC30037] Character is not valid.
Any ideas? Please help!Replace @.startDate with Parameters!StartDate.Value
GeoSYnch
"SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
news:CC20E91E-80CC-4CF0-AB8F-D02FCD513B73@.microsoft.com...
>I have the following in a textbox expression:
> =datepart("d",dateadd("d", 3, @.startDate))
> I have this or something similar in many textboxes and they are all
> returning the following error:
> The value expression for the textbox 'textbox24' contains an error:
> [BC30037] Character is not valid.
> Any ideas? Please help!
>
>|||THANK YOU!!!!!!
"GeoSynch" wrote:
> Replace @.startDate with Parameters!StartDate.Value
>
> GeoSYnch
>
> "SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
> news:CC20E91E-80CC-4CF0-AB8F-D02FCD513B73@.microsoft.com...
> >I have the following in a textbox expression:
> >
> > =datepart("d",dateadd("d", 3, @.startDate))
> >
> > I have this or something similar in many textboxes and they are all
> > returning the following error:
> >
> > The value expression for the textbox 'textbox24' contains an error:
> > [BC30037] Character is not valid.
> >
> > Any ideas? Please help!
> >
> >
> >
>
>