• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Is it possible to create an order number, like yy-### (07-195) and it would reset at the beginning of each new year?

In SQL Server, is it possible to reset the PK identity column integer back to 1 at 12:00:01 AM on January 1rst of each year? I would like to make an order form that uses the date combined with a new generated id number when the user creates a new order, e.g; Order Number yy-### ( 07-015). Or can this be done programatically in my VB app by code?

Thanks
0
grouchyfd
Asked:
grouchyfd
  • 2
  • 2
1 Solution
 
cezarFCommented:
this might help..
http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/

but i would not suggest  resetting it back to 1.  you can instead reset it to the nearest thousand or ten thousand.  e.g at the end of the year you have like 897 then reset it to 1000 so that the next value would be 1001. then just pick the last 3 digits of the current value.
0
 
answer_meCommented:
Better not reseed the identity column only change the year part from the UI.
0
 
cezarFCommented:
i think he is more concern about the ### part of the order number. :)
0
 
grouchyfdAuthor Commented:
I guess I could set the Identity data type to nvarchar so it will accept any text. I guess what I need help with is to figure out in VB how to set a textbox to create the 2 digit year combined with the number. I can use a Command Text in VB to reset the identity seed. I was wondering if there is a way to have the system datetime picked up and reset the year to the current year after 12:00:01 on Jan 1.
0
 
grouchyfdAuthor Commented:
I have found that I need to send this out to the VB developers. But thank you for your help with this because I will be able to reset the identity seed in the table.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now