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 null in Sql Server database

Hi,

I'm using this source code in order to set the DateTime field of my Sql Server database to null.
I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.

I have an error saying that 'object' does not contain definition for 'Value'.

In french :Message d'erreur du compilateur:CS0117: 'object' ne contient pas de définition pour 'Value'
dbCommand.Parameters["@.DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;

The funny thing is that in the class browser i can see the Value property for the class Object...

C#, asp.net
string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = "insert into Emp (Date) Values (@.Date) ";
conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null;
if (myDate == DateTime.MinValue)
{
cmd.Parameters ["@.Date"].Value =sqldatenull ;
}
else
{
cmd.Parameters["@.Date"].Value = myDate;
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = "Record Inserted Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

Are you sure you're referencing the correct Parameter? The error message says "@.DataRDV" but your code uses "@.Date".

|||

GranPas wrote:

Hi,

I'm using this source code in order to set the DateTime field of my Sql Server database to null.
I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.

I have an error saying that 'object' does not contain definition for 'Value'.

In french :Message d'erreur du compilateur:CS0117: 'object' ne contient pas de définition pour 'Value'
dbCommand.Parameters["@.DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;

The funny thing is that in the class browser i can see the Value property for the class Object...

C#, asp.net
string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = "insert into Emp (Date) Values (@.Date) ";
conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null;
if (myDate == DateTime.MinValue)
{
cmd.Parameters ["@.Date"].Value =sqldatenull ;
}
else
{
cmd.Parameters["@.Date"].Value = myDate;
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = "Record Inserted Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

Did you add a parameter called "@.DateRDV"?

|||

Thanks for help. I actually changed my variable name which was DateRDV to Date because the source code i had pasted was from a sample i found on Internet.

I found a solution to my problem. When the user click on a button, I set myDate to DateTime.MinValue if myDate is null, as i did before. Now, I am using a function in order to insert the date in my database. This is working and I still don't know why the older source code did not. Here is my source working :

int Insert_Trdv(System.DateTime dateRDV)
{
string connectionString = "server=\'myServer\'; user id=\'myId\';
password=\'myPassword\'; database=\'myPassword\'";
System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString = @."INSERT INTO [Trdv] ([DateRDV])";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter dbParam_dateRDV = new
System.Data.SqlClient.SqlParameter();
dbParam_dateRDV.ParameterName = "@.DateRDV";
if(dateRDV == DateTime.MinValue)
{
dbParam_dateRDV.Value = DBNull.Value;
}
else
{
dbParam_dateRDV.Value = dateRDV;
}
dbParam_dateRDV.DbType = System.Data.DbType.DateTime;
dbCommand.Parameters.Add(dbParam_dateRDV);
int rowsAffected = 0;
dbConnection.Open();
try
{
rowsAffected = dbCommand.ExecuteNonQuery();
}
finally
{
dbConnection.Close();
}
return rowsAffected;
}

Thx

Dates function for Reporting Services

Excel has a function called workday which takes out weekends and holidays and give you the date of when the project will be done
I'm can't find any function that does what workday function does in Reporting services.

DateAdd includes the weekends and holiday which i don't need.
I need something that can do something close to workday in excel

There is no such function that I am aware of. You can obviously approximate it by

@.WANTED = DATEADD(Day, 1.4 * @.WorkDays. @.StartDate)

To go beyond that requires a table of Holidays and a more involved function. There may be such a function i SQLCENTRAL.COM.

|||

Use

CREATE FUNCTION dbo.fnAddWorkdays(@.FROM DATETIME, @.DAYS INT)
-- Purpose:
-- Calculate Date @.DAYS in future from @.FROM
-- Copyright (C) 2007 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
RETURNS DATETIME AS
BEGIN
DECLARE @.YYYYMMDD CHAR(8)
WHILE @.DAYS > 0
BEGIN
SET @.FROM = DATEADD(day, 1, @.FROM)
SET @.YYYYMMDD = SUBSTRING(REPLACE(CONVERT(CHAR(20), @.FROM, 126), '-', ''), 1, 8)
IF DATEPART(weekday,@.FROM) NOT IN (1, 7) BEGIN
IF NOT EXISTS(SELECT * FROM Holiday WHERE YYYYMMDD = @.YYYYMMDD) SET @.DAYS = @.DAYS - 1
END
END
RETURN @.FROM
END
GO
SELECT dbo.fnAddWorkdays(GETDATE(), 1)
SELECT dbo.fnAddWorkdays(GETDATE(), 2)
SELECT dbo.fnAddWorkdays(GETDATE(), 3)

|||

Table create is

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Holiday]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Holiday] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[YYYYMMDD] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[Holiday] WITH NOCHECK ADD
CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Holiday] ON [dbo].[Holiday]([YYYYMMDD]) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'Date in yyyyMMdd notation', N'user', N'dbo', N'table', N'Holiday', N'column', N'YYYYMMDD'

GO

dates

Hello Everyone,
I am working on a database table that was imported from an excel spreadsheet
where the date column value is stored as follow(s)
yyyymmdd this seems to be stored as a numeric value and not as a date value.
So I am trying to write a query on this table where I want to break the data
down by months or by w number. However, I am running into a few problems
with this field because of the way the value is stored as a number and not
necessary as a date field. I was wondering if any one had any ideas on how
to query this field.
J. D.Try the expression: CAST( CAST( dt AS VARCHAR ) AS DATETIME )
Anith|||If all the values in this column are 8-digit integers that would be valid
dates in yyyymmdd format, you should be able to refer to
CAST(CAST(yourColumn as char(8)) as datetime)
and then use any date functions you need.
Steve Kass
Drew University
JD wrote:

>Hello Everyone,
>I am working on a database table that was imported from an excel spreadshee
t
>where the date column value is stored as follow(s)
>yyyymmdd this seems to be stored as a numeric value and not as a date value
.
>So I am trying to write a query on this table where I want to break the dat
a
>down by months or by w number. However, I am running into a few problems
>with this field because of the way the value is stored as a number and not
>necessary as a date field. I was wondering if any one had any ideas on how
>to query this field.
>
>