Id reset every 12am

[Eduard Lu]

I have a table named tblTransaction

ID int (auto increment)
TransactionID nvarchar(50)
TransactionDate datetime
TransactionType nvarchar(50)

now, I want my TransactionID to be in the format of <mmddyy99999999> or 'TransactionDate + ID'. But the problem is that I need to reset my seed every 12AM so that my TransactionID the next day will be 0709110 then 0709111, and so on.... Can someone help me with my problem? Thanks in advance!

[nbiswas]

I want my TransactionID to be in the format of <mmddyy99999999> or 'TransactionDate + ID'.

Solution

Select Cast(ID as Varchar(10)) + REPLACE(CONVERT (VARCHAR(10), TransactionDate, 101), '/', '') As TransactionID

But the problem is that I need to reset my seed every 12AM so that my TransactionID the next day will be 0709110 then 0709111

Run a job scheduler or a background process that will be triggered precisely at 12AM everyday

Hope you got the idea.

[Rabbit]

REPLACE(CONVERT(VARCHAR(10), TransactionDate, 101), '/', '')
+
CAST(ID - 
   (SELECT MAX(ID)
    FROM tblName
    WHERE TransactionDate < CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101) + ' 00:00:00.000')) AS VARCHAR(10))