How to find out the number of days in a financial

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to find out the number of days in a financial

Author

Topic

rajukotla
Starting Member

9 Posts

Posted - 2008-02-04 : 06:17:51

How to find out the number of days in a financial year.

suppose from date is sep1st,2006.and to date is aug24th,2009.

the entire range involves financial years.i.e.,

april1st,2006-march31st,2007
april1st,2007-march31st,2008
april1st,2008-march31st,2009
april1st,2009-march31st,2010.
we have to calculate the no.of days of last financial year..
waiting for reply..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 06:27:04

DATEDIFF(d,@fromdate,@todate)

rajukotla
Starting Member

9 Posts

Posted - 2008-02-04 : 06:32:02

quote:
Originally posted by visakh16

DATEDIFF(d,@fromdate,@todate)

this will work for normal calculation of DATEDIFF.but i need the solution for what i have said..
first we have to calculate the financial year which we are going to find out the no.of days.
i posted the entire details..try to find..

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-04 : 06:34:24

do you actually mean number of 'working days', rather than just number of days?

Em

rajukotla
Starting Member

9 Posts

Posted - 2008-02-04 : 06:41:11

quote:
Originally posted by elancaster

do you actually mean number of 'working days', rather than just number of days?

Em

i need to calculate no.of days in a financial year(not no.of working days)..
if leap year exists,366.
otherwise 365

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 06:55:50

one method. not efficient one i guess:-


declare @range varchar(60)
select @range='april1st,2008-march31st,2009'
Declare @FromDate varchar(20),@ToDate varchar(20)
SELECT @FromDate=LEFT(@range,CHARINDEX('-',@range)-1),
@ToDate=RIGHT(@range,LEN(@range)-CHARINDEX('-',@range))
SELECT @FromDate,@ToDate
SELECT DATEDIFF(d,SUBSTRING(@FromDate,PATINDEX('%[0-9]%',@FromDate),PATINDEX('%,%',@FromDate)-2-PATINDEX('%[0-9]%',@FromDate))+ LEFT(@FromDate,PATINDEX('%[0-9]%',@FromDate)-1) + ' ' + RIGHT(@FromDate,4),
SUBSTRING(@ToDate,PATINDEX('%[0-9]%',@ToDate),PATINDEX('%,%',@ToDate)-2-PATINDEX('%[0-9]%',@ToDate))+ LEFT(@ToDate,PATINDEX('%[0-9]%',@ToDate)-1) + ' ' + RIGHT(@ToDate,4))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:02:19

Another method

-- Initialize user supplied parameter
DECLARE	@FinancialYear SMALLINT

SET	@FinancialYear = 2007

-- Show the expected result
SELECT	StartDate,
	EndDate,
	1 + DATEDIFF(DAY, StartDate, EndDate) AS Days
FROM	(
		SELECT	DATEADD(YEAR, @FinancialYear - 1900, '19000401') AS StartDate,
			DATEADD(YEAR, @FinancialYear - 1899, '19000331') AS EndDate
	) AS x

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:15:49

Or the complete solution

CREATE PROCEDURE dbo.uspGetFinancialYears
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
AS

-- Do not allow unexpected resultsets back to client
SET NOCOUNT ON

-- Make sure parameter values are assigned in ascending order
DECLARE	@Temp DATETIME

IF @FromDate > @ToDate
	SELECT	@Temp = @FromDate,
		@FromDate = @ToDate,
		@ToDate = @Temp

-- Show the expected output
SELECT		g.StartDate,
		g.EndDate,
		1 + DATEDIFF(DAY, g.StartDate, g.EndDate) AS Days
FROM		(
			SELECT	DATEADD(YEAR, v.Number + YEAR(@FromDate) - 1900, '19000401') AS StartDate,
				DATEADD(YEAR, v.Number + YEAR(@FromDate) - 1900, '19010331') AS EndDate
			FROM	master..spt_values AS v
			WHERE	Type = 'p'
				AND v.Number <= DATEDIFF(YEAR, @FromDate, @ToDate)
		) AS g
WHERE		@FromDate <= g.EndDate
		AND @ToDate >= g.StartDate
ORDER BY	g.StartDate

Call with EXEC dbo.uspGetFinancialYears '20060901', '20090824'

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 07:19:00

quote:

Originally posted by rajukotla

quote:

Originally posted by visakh16

one method. not efficient one i guess:-


declare @range varchar(60)
select @range='april1st,2008-march31st,2009'
Declare @FromDate varchar(20),@ToDate varchar(20)
SELECT @FromDate=LEFT(@range,CHARINDEX('-',@range)-1),
@ToDate=RIGHT(@range,LEN(@range)-CHARINDEX('-',@range))
SELECT @FromDate,@ToDate
SELECT DATEDIFF(d,SUBSTRING(@FromDate,PATINDEX('%[0-9]%',@FromDate),PATINDEX('%,%',@FromDate)-2-PATINDEX('%[0-9]%',@FromDate))+ LEFT(@FromDate,PATINDEX('%[0-9]%',@FromDate)-1) + ' ' + RIGHT(@FromDate,4),
SUBSTRING(@ToDate,PATINDEX('%[0-9]%',@ToDate),PATINDEX('%,%',@ToDate)-2-PATINDEX('%[0-9]%',@ToDate))+ LEFT(@ToDate,PATINDEX('%[0-9]%',@ToDate)-1) + ' ' + RIGHT(@ToDate,4))

i think u didnt understand my prob

what i understood was you need to return number of days between the range given in format date StartDate-EndDate. Wasnt it your requirement?

rajukotla
Starting Member

9 Posts

Posted - 2008-02-04 : 07:23:27

quote:
Originally posted by visakh16

quote:
Originally posted by rajukotla

quote:
Originally posted by visakh16

one method. not efficient one i guess:-

declare @range varchar(60)
select @range='april1st,2008-march31st,2009'
Declare @FromDate varchar(20),@ToDate varchar(20)
SELECT @FromDate=LEFT(@range,CHARINDEX('-',@range)-1),
@ToDate=RIGHT(@range,LEN(@range)-CHARINDEX('-',@range))
SELECT @FromDate,@ToDate
SELECT DATEDIFF(d,SUBSTRING(@FromDate,PATINDEX('%[0-9]%',@FromDate),PATINDEX('%,%',@FromDate)-2-PATINDEX('%[0-9]%',@FromDate))+ LEFT(@FromDate,PATINDEX('%[0-9]%',@FromDate)-1) + ' ' + RIGHT(@FromDate,4),
SUBSTRING(@ToDate,PATINDEX('%[0-9]%',@ToDate),PATINDEX('%,%',@ToDate)-2-PATINDEX('%[0-9]%',@ToDate))+ LEFT(@ToDate,PATINDEX('%[0-9]%',@ToDate)-1) + ' ' + RIGHT(@ToDate,4))

i think u didnt understand my prob

what i understood was you need to return number of days between the range given in format date StartDate-EndDate. Wasnt it your requirement?

no..first i need to calculate the latest financial year within that range..and next we have to find the no.of days with in that latest financial year

rajukotla
Starting Member

9 Posts

Posted - 2008-02-04 : 07:33:21

quote:

Originally posted by Peso

Another method

-- Initialize user supplied parameter
DECLARE	@FinancialYear SMALLINT

SET	@FinancialYear = 2007

-- Show the expected result
SELECT	StartDate,
	EndDate,
	1 + DATEDIFF(DAY, StartDate, EndDate) AS Days
FROM	(
		SELECT	DATEADD(YEAR, @FinancialYear - 1900, '19000401') AS StartDate,
			DATEADD(YEAR, @FinancialYear - 1899, '19000331') AS EndDate
	) AS x

E 12°55'05.25"
N 56°04'39.16"

thanks !!
u r initialising the year na..
but in my case we have to find out the year and go for the process that u have did..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:36:44

Did you see my complete suggestion for a solution posted 02/04/2008 : 07:15:49 ?

E 12°55'05.25"
N 56°04'39.16"

rajukotla
Starting Member

9 Posts

Posted - 2008-02-04 : 07:42:21