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 problem

an existing application sends server an sql string like
insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
where c1 is an int, and c2 is a datetime field. This command returns an erro
r.
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
when I change that command like following
SET DATEFORMAT dmy
insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
it works fine.
I want to set server always accepts dates im dmy format.
What can I do for this.
Thanks in advanceCould you instead pass dates in the following format? It always works:
YYYYMMDD HH:MM:SS
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote in
message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> an existing application sends server an sql string like
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> where c1 is an int, and c2 is a datetime field. This command returns an
error.
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> when I change that command like following
> SET DATEFORMAT dmy
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> it works fine.
> I want to set server always accepts dates im dmy format.
> What can I do for this.
> Thanks in advance|||that way requires application change. Actually there is lots of data in dmy
format. When server changed to SQL 2000, application get following error
message from server. It was work fine with previous version SQL server, but
not SQL 2000
"Narayana Vyas Kondreddi" wrote:

> Could you instead pass dates in the following format? It always works:
> YYYYMMDD HH:MM:SS
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote
in
> message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> error.
>
>|||This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...
datetime.asp, more
specifically rl]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote in
message
news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
> that way requires application change. Actually there is lots of data in dm
y
> format. When server changed to SQL 2000, application get following error
> message from server. It was work fine with previous version SQL server, bu
t
> not SQL 2000
> "Narayana Vyas Kondreddi" wrote:
>|||When I set logins default language by enterpirse manager, it runs normal.
Thanks for help :)
"Tibor Karaszi" wrote:

> This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...o_datetime.asp, more
> specifically /url]
> --
> Tibor Karaszi, SQL Server MVP
> [url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote i
n message
> news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
>|||Create INSTEAD OF trigger on your table and reformat an input in it.
"Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote in
message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> an existing application sends server an sql string like
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> where c1 is an int, and c2 is a datetime field. This command returns an
> error.
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> when I change that command like following
> SET DATEFORMAT dmy
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> it works fine.
> I want to set server always accepts dates im dmy format.
> What can I do for this.
> Thanks in advance

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?
It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>
|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>> Using SQL2005 DTS - I am trying to import data from a CSV file into a
>> table created with the following
>> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
>> [Date-Time] datetime,
>> [Server] text,
>> [Drive] text,
>> [Drive Size] numeric(29,0),
>> [Space Free] numeric(29,0)
>> )
>> the first field is date and time and looks like this >>
>> 09/20/2006 06:30:03 PM
>> But no matter what I try to use for a final field format the result of
>> that data after it's imported displays the same time for every record >>
>> 12:00:00 AM <<. The date comes through fine, but it just does not seem
>> to recognize the time. What do I need to do to get the time to be
>> imported correctly ?
>

datetime

create proc dbo.GetList
(
@.OrgList varchar(1000),
@.startDateTime datetime

)
as
begin

declare @.SQL varchar(1000)

set @.SQL = 'Select A.TransactionID,
A.PermitId,
A.IssuingOrganizationId,
A.VehicleId

From Vehicle A WITH (NOLOCK)
join PurchasingCompany B WITH (NOLOCK)
on A.PurchasingCompanyId = B.PurchasingCompanyId
Where
A.IssueDate >= '+ '@.startDateTime' +' And
A.IssuingOrganizationId IN ('+ @.OrgList+')'
exec(@.sql)
end
go

This doesn't work, But If I substitute@.startDateTime with '2/1/2004', it works. I think iam missing some formatting, I tried several ways to make it work. Could anyone tell me how I should do this.

You are including the literal "@.StartDateTime", which is clearly not what you want.

create proc dbo.GetList
(
@.OrgList varchar(1000),
@.startDateTime datetime

)
as
begin

Look at the BOL article on CONVERT to determine what format is right for you - I am using the ODBC canonical format.

declare @.SQL varchar(1000)

set @.SQL = 'Select A.TransactionID,
A.PermitId,
A.IssuingOrganizationId,
A.VehicleId

From Vehicle A WITH (NOLOCK)
join PurchasingCompany B WITH (NOLOCK)
on A.PurchasingCompanyId = B.PurchasingCompanyId
Where
A.IssueDate >= '''+ CONVERT(nvarchar(30),@.startDateTime,120) +''' And
A.IssuingOrganizationId IN ('+ @.OrgList+')'
exec(@.sql)
end
go

|||That really worked for me, thank you so much for the reply.

DateDiff question

Technically, what is the difference between these two pieces:
datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
I know one does day and one does day of year and their results are slightly
different, so which one will actually give me a record that is >= one year
old?
WillieIf you want all the rows more then 1 year old and you also want to correctly
handle leap years, why not use the following:
WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), dtInsertDate,
101))
--Brian
(Please reply to the newsgroups only.)
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23bviLhkuFHA.3400@.TK2MSFTNGP14.phx.gbl...
> Technically, what is the difference between these two pieces:
> datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> I know one does day and one does day of year and their results are
> slightly different, so which one will actually give me a record that is >=
> one year old?
> Willie
>|||Sorry, I'm a little slow sometimes, but do you mean
WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), getdate(),
101))? Otherwise I don't see how it gets today's date to check from? And
then, would it work to just use getdate() instead of the whole convert
thing, or do I need that to properly handle the dateadd?
Thanks,
Willie
"Brian Lawton" <brian.k.lawton@.redtailcr.com> wrote in message
news:u%23BqQJluFHA.664@.tk2msftngp13.phx.gbl...
> If you want all the rows more then 1 year old and you also want to
> correctly handle leap years, why not use the following:
> WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), dtInsertDate,
> 101))
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23bviLhkuFHA.3400@.TK2MSFTNGP14.phx.gbl...
>|||Sorry about that. You're correct, the GETDATE () needs to be inside of the
convert. I included the CONVERT to ensure that the time portion of the
GETDATE() return value is truncated thereby forcing a consistent comparison
to midnight rather than arbitrary comparison based on the current run time.
--Brian
(Please reply to the newsgroups only.)
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:OprzcbuuFHA.3048@.TK2MSFTNGP10.phx.gbl...
> Sorry, I'm a little slow sometimes, but do you mean
> WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), getdate(),
> 101))? Otherwise I don't see how it gets today's date to check from? And
> then, would it work to just use getdate() instead of the whole convert
> thing, or do I need that to properly handle the dateadd?
> Thanks,
> Willie
> "Brian Lawton" <brian.k.lawton@.redtailcr.com> wrote in message
> news:u%23BqQJluFHA.664@.tk2msftngp13.phx.gbl...
>|||On Thu, 15 Sep 2005 15:53:39 -0700, Willie Bodger wrote:

>Technically, what is the difference between these two pieces:
> datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
Hi Willie,
As far as I know, there is no diffference at all. The y parameter only
differs from the day parameter in the context of the DATEPART function,
not in the context of DATEDIFF.

>I know one does day and one does day of year and their results are slightly
>different, so which one will actually give me a record that is >= one year
>old?
Could you post an example where the results are different? I ust tested
it on a cross self-join of a table with all dates from 2000 up to and
including 2005, and I didn't found a single combination of dates where
they differ.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Datediff problem

Any one please tell me how to fix the view..it's producing lots of errors -

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT E.EmpNo,

IF E.ResignDate IS NOT NULL

MonthWorked = DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

MonthWorked = DATEDIFF( M, E.JoinDate, GETDATE() )

FROM Employees E

Code Snippet

Msg 156, Level 15, State 1, Procedure vw_Issues_Deducts, Line 10

Incorrect syntax near the keyword 'IF'.

Msg 102, Level 15, State 1, Procedure vw_Issues_Deducts, Line 11

Incorrect syntax near 'MonthWorked'.

Regards

Kapalic

You have to use the CASE WHEN...

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT

E.EmpNo,

MonthWorked =

Case When E.ResignDate IS NOT NULL

DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

MonthWorked = DATEDIFF( M, E.JoinDate, GETDATE() )

END

FROM

Employees E

|||

Still geting error -

Code Snippet

Msg 102, Level 15, State 1, Procedure vw_Issues_Deducts, Line 16
Incorrect syntax near 'DATEDIFF'.

Regards

Kapalic

|||

Fixed it!

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT E.EmpNo,

MonthWorked = Case When E.ResignDate IS NOT NULL THEN

DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

DATEDIFF( M, E.JoinDate, GETDATE() )

END

FROM Employees E

Regards

Kapalic