How to increment Custom Unique ID?

I was going to post this as a comment to solution 1 but it's easier to get the formatting with a "solution" than a comment ...

You said you already had a unique ID and I thought a bit more about the "unique counter within a date". This CP article gave me the idea - How to use ROW_NUMBER() to enumerate and partition records in SQL Server[^]

Using that principle I wanted to create a unique-within-a-date identifier...

I created a sample table with this

CREATE TABLE [dbo].[CP1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [recDate] [date] NOT NULL,
    [someData] [nchar](10) NULL,
    [NewId] [int] NULL
) ON [PRIMARY]

I won't repeat any sample data I inserted (unless you ask me to) but the thing I was trying to get to was not having to generate another unique ID.

This sql query will "format" your unique ID based on the date and identity column

WITH T1(ID, recDate, someData, NewId)
AS
(
    SELECT [ID],recDate,someData,
     ROW_NUMBER() over(PARTITION BY recDate
                         ORDER BY ID ASC) Number
    FROM CP1
)
select ID, recDate, someData, 'M' +
        REPLACE(CONVERT(CHAR(10), recDate, 103), '/', '')
        + Replace(Str(NewId, 7), ' ' , '0')
        from T1 where ID=@input

where ID=@input is however you want to identify the record you are after.

You could put this into a insert trigger on the table to generate the ID and persist it on the table, or use it when extracting for display.

One important caveat - if the ID is generated at display time only, it would change if records had been deleted. If persisted (via a trigger) then it should work regardless.

Please note this is meant to be complementary to Solution 1 - not an alternative