Function in SQLServer - SQL Server Forums

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

Transact-SQL (2000)

Function in SQLServer

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2004-03-09 : 07:59:12

Hi there

In my table I had field called LeaseStartDate and LeaseEndDate. It's basically smalldatime data type (eq 12/01/2004).

Due to financial year it's starting from July till June, is there any in-built function in MsSQLServer to find the index of 7 (July) for instance is equal to 0, 8 (Aug) = 1, 9 (Sep) = 2 and soon.

This kind of thing is easy doing in the code level using an array like: Array(7,8,9,10,11,12,1,2,3,4,5,6) which from this array I can just do the comparison between each quite easily cause I know the index is .. for instance 12 (Dec) is less than 5 (May) due to the index of 12 (dec) = 5 while the 5 (May) = 10.

Thanks, Valdy

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-09 : 08:45:54

CREATE TABLE #DateTest(MonthNo INT, TestDate DATETIME)

INSERT INTO #DateTest VALUES(1, '2003-07-15')
INSERT INTO #DateTest VALUES(2, '2003-08-15')
INSERT INTO #DateTest VALUES(3, '2003-09-15')
INSERT INTO #DateTest VALUES(4, '2003-10-15')
INSERT INTO #DateTest VALUES(5, '2003-11-15')
INSERT INTO #DateTest VALUES(6, '2003-12-15')
INSERT INTO #DateTest VALUES(7, '2004-01-15')
INSERT INTO #DateTest VALUES(8, '2004-02-15')
INSERT INTO #DateTest VALUES(9, '2004-03-15')
INSERT INTO #DateTest VALUES(10, '2004-04-15')
INSERT INTO #DateTest VALUES(11, '2004-05-15')
INSERT INTO #DateTest VALUES(12, '2004-06-15')

SELECT MonthNo, TestDate, CASE WHEN MONTH(TestDate) BETWEEN 7 AND 12 THEN MONTH(TestDate) - 6 ELSE MONTH(TestDate) + 6 END
FROM #DateTest
ORDER BY 1

--For Brett
DROP TABLE #DateTest

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-09 : 11:36:00

Nice!

How about



--For Duane

USE Northwind
GO 

CREATE TABLE #DateTest(MonthNo int, TestDate datetime)
GO

INSERT INTO #DateTest(MonthNo, TestDate) 
SELECT 1,  '2003-07-15' UNION ALL
SELECT 2,  '2003-08-15' UNION ALL
SELECT 3,  '2003-09-15' UNION ALL
SELECT 4,  '2003-10-15' UNION ALL
SELECT 5,  '2003-11-15' UNION ALL
SELECT 6,  '2003-12-15' UNION ALL
SELECT 7,  '2004-01-15' UNION ALL
SELECT 8,  '2004-02-15' UNION ALL
SELECT 9,  '2004-03-15' UNION ALL
SELECT 10, '2004-04-15' UNION ALL
SELECT 11, '2004-05-15' UNION ALL
SELECT 12, '2004-06-15'


  SELECT  MonthNo
	, TestDate
	, MONTH(DATEADD(mm,6,TestDate)) AS Fiscal_Month 
    FROM #DateTest
ORDER BY 1

DROP TABLE #DateTest

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-09 : 22:07:55

Good!

Duane.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-09 : 22:28:18

(CalendarMonth - (start month of fiscal year) % 12) + 1

will return the month# for the fiscal year.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources