mysql auto increment in dd mm xxx format

Solutions on MaxInterview for mysql auto increment in dd mm xxx format by the best coders in the world

Search
showing results for - "mysql auto increment in dd mm xxx format"
Élodie
26 Oct 2019
1 /****** Object:  Trigger [dbo].[customAutoIncrement]
2
3        SET ANSI_NULLS ON
4        GO
5
6        SET QUOTED_IDENTIFIER ON
7        GO
8
9         CREATE TRIGGER [dbo].[customAutoIncrement]
10         ON [dbo].[Products_SumOrders]
11        AFTER INSERT
12        AS 
13        BEGIN
14            SET NOCOUNT ON;
15            declare @maxOrderID int;
16            declare @maxOrderRef nvarchar(max);
17            declare @relativeID varchar(max);
18            declare @orderId int;
19            select @orderId =orderid from inserted;
20            print(@orderId);--allow me to display the inserted order id
21            SET @maxOrderID = (SELECT Max(orderid) FROM [Products_SumOrders] WHERE MONTH([OrderDate]) = (MONTH(GETDATE())) AND YEAR([OrderDate]) = YEAR(GETDATE()) and orderid < @orderId );
22                print(@maxOrderID);--last added order
23    --custom month format (always MM):
24                declare @mon int;
25                declare @stringMon nvarchar(10);
26                set @mon= MONTH(GETDATE());
27                IF @mon <10
28                set @stringMon ='0' +  CONVERT(VARCHAR(1),@mon) ;
29                ELSE
30                set @stringMon = CONVERT(VARCHAR(2),@mon) ;
31            IF @maxOrderID is null --no orders has been added in this month:
32            begin
33                set @maxOrderRef =  Substring(CONVERT(VARCHAR(4),YEAR(GETDATE())),3,4)+ '-'+  CONVERT(VARCHAR(2),@stringMon)+'-001';
34            end
35            ELSE
36--custom order id format (always XXX):
37            begin
38            set @relativeID =(SELECT [purchaseOrderRef] FROM [Products_SumOrders] WHERE orderid=@maxOrderID);
39            set @relativeID = Substring(@relativeID,LEN(@relativeID)-(Charindex('-', REVERSE(@relativeID))-2),LEN(@relativeID));
40            print(CONVERT(int,@relativeID));
41            IF CONVERT(int,@relativeID) < 9
42                set @relativeID ='00' +  CONVERT(VARCHAR(2),@relativeID+1) ;
43                ELSE
44                begin
45                if CONVERT(int,@relativeID) < 99
46                set @relativeID ='0' +  CONVERT(VARCHAR(3),@relativeID+1) ;
47                else
48                set @relativeID = CONVERT(VARCHAR(3),@relativeID+1) ;
49                end
50            set @maxOrderRef =  Substring(CONVERT(VARCHAR(4),YEAR(GETDATE())),3,4)+ '-'+  CONVERT(VARCHAR(2),@stringMon)+'-'+ CONVERT(VARCHAR(3),@relativeID);
51            end
52            print(@maxOrderRef);
53            UPDATE Products_SumOrders
54            SET purchaseOrderRef =  @maxOrderRef
55            FROM inserted  INNER JOIN [Products_SumOrders]  On inserted.orderid = [Products_SumOrders].orderid
56        END 
57
58        GO
similar questions
alter table auto incrementoracle auto incrementmysql check auto increment valueadd auto increment column to existing table mysqlinsert into auto increment mysqlauto increment column in mysql query resultshow to change the auto increment in existing table mysqlsql mm 2fdd 2fyyyy formatzerar auto increment mysqlauto increment mysqlsql server date format yyyy mm ddthh 3amm 3asscreate table primary key auto increment mysqloracle add auto increment column to existing tablemysql remove auto incrementmake a field auto increment mysqlpl sql auto incrementsql server date format yyyy mm ddhow to set an already made tables auto increment in mysqlmysql set id auto incrementmysql ddl
queries leading to this page
mysql auto increment in yy mm xxx formatmysql auto increment in dd mm xxx formatmysql auto increment default value with prefix month and yearmysql auto increment in dd mm xxx format