Hi, I am trying to create a unique ID for each record based on the date the record is created. Example, if the record is created today, I want the key to be 20101130XX where XX would be a sequential list of numbers such as 01, 02, 03 etc... in case more than one person creates a record today. If 3 people created records yesterday their unique IDs would be 2011032700 2011032701 2011032702 and then midnight comes and someone creates a new record it would be 2011032800 The purpose of this is to give each record a unique ID that will be used to reference the ticket. We are already using the date format, but currently users are having to manually enter the id and that can create errors such as 2012032700 when it should have been 2011032700 so instead of putting 2011 they put 2012 I am just not sure how to create a trigger to generate this type of unique ID and would appreciate any assistance
Why not include the time down to the millisecond at the end of the date? Then you don't have to generate 01, 02, 03, etc.
It's not really "user friendly" to expect people to input a date in YYYYMMDD format - normal people don't think like this. Why not just have an auto increment as the key and use this as the ticket number? The users are going to put in less than 8 digits (which is what they seem to do now) in order to find their ticket.