SQL Serverver--Create fiscal year table (constraint)

--Example 1: Create financial year table (constraint)

--New table:

Create Table Fiscalyears
(
Fiscal_Year INTEGER not NULL PRIMARY KEY,
Start_date date not NULL,
Constraint valid_start_date
CHECK (
Year (start_date) = Fiscal_year-1
and MONTH (start_date) =10
And Day (start_date) =01
),

End_date date not NULL,
Constraint valid_end_date
CHECK (
Year (end_date) = Fiscal_Year
and MONTH (end_date) =10
And Day (end_date) =01
)
);

--Delete constraint valid_end_date

Alter Table Fiscalyears
Drop constraint Valid_end_date

--New constraint
Alter Table Fiscalyears
Add Constraint Valid_end_date
CHECK (
Year (end_date) = Fiscal_Year
and MONTH (end_date) =09
And Day (end_date) =30
)

-OR:

ALTER TABLE [dbo]. [Fiscalyears]
With CHECK
ADD CONSTRAINT [Valid_end_date]
CHECK (
(
DATEPART (Year,[end_date]) =[fiscal_year]
and DATEPART (month,[end_date]) = (9)
and DATEPART (day,[end_date]) = (30)
)
)
GO

--Insert data into table Fiscalyears
INSERT INTO dbo. Fiscalyears
Values
' 1990 ',
' 1989-10-01 ',
' 1990-09-30 '
)

--Reference: http://www.xuebuyuan.com/53328.html;

--http://m.blog.csdn.net/blog/zhaoyh0530/4535987

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1699518

SQL Serverver--Create fiscal year table (constraint)