sql - Change number automatically with function

Question

I have a simple function that get a number and sum + 1:

ALTER FUNCTION [dbo].[GetNextProjectNumber] ()  
RETURNS INT
AS  
BEGIN
    DECLARE @LegacyKey INT

    SELECT @LegacyKey = (MAX(CAST(LegacyKey AS INT)) + 1) 
    FROM Project; 

    RETURN @LegacyKey;
END

So every year we change @LegacyKey number for new one manually like:

In 2017: 18000
In 2018: 19000
In 2019: 20000
etc..

This happen every 1st of October, so I want to know if its possible to change it automatically.

Today value should be 20000, once I change it it should sum + 1 since October of 2020, in October of 2020 it should change to 21000

How can I achieve that?

标签： sqlsql-servertsql

Answer 1

这是另一种选择：

select (year(dateadd(month, 3, getDate())) - 2000) * 1000

示例 1：

select (year(dateadd(month, 3, '2019-09-30 13:29:07.433')) - 2000) * 1000

退货

19000

示例 2：

select (year(dateadd(month, 3, '2020-10-01 13:29:07.433')) - 2000) * 1000

退货

21000

通过在当前日期加上 3 个月，我们能够确保在乘以 1000 时选择“下一个”年份而不是当前年份。