Generate an id starting from one on every new date in C# using SQL server database

Question

Please help me to do this. Generate an id from database but id should start from one on each new date. Using c# and SQL server.

Answer 1

Please modify below SQL procedure based on your requirement

create proc [dbo].[InsertRecord]
    as
    begin
        declare @ID int=0;
        if (select count(1) from table_1 where CONVERT(VARCHAR(10),CurrentDate,10)=CONVERT(VARCHAR(10),GETDATE(),10))=0
            begin
                set @ID=1;
            end
        else 
            begin
                set @ID=(select top 1 ID from table_1 order by CurrentDate desc);
                set @ID=isnull(@ID,0)+1;
            end

        insert into table_1(ID,CurrentDate)values(@ID,GETDATE());
        select * from table_1;
    end