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

Posted on 2007-10-17
Last Modified: 2013-11-26
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?

Question by:grouchyfd
    LVL 14

    Accepted Solution

    this might help..

    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.
    LVL 10

    Expert Comment

    Better not reseed the identity column only change the year part from the UI.
    LVL 14

    Expert Comment

    i think he is more concern about the ### part of the order number. :)

    Author Comment

    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.

    Author Comment

    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.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now