Generating A Unique Number Using Date and Time

Question

_{Friday, June 30, 2006 5:14 PM}

I'm trying to create a function that will generate a unique number based on the date and time a record is saved to a SQL Server database.  I would like to get the number in the following format:  yymmddhhss  (i.e. year/month/day/hour/sec).  Anybody know how I might go about creating this function?

Thanks in advance for any help!

Tony

All replies (5)

_{Friday, June 30, 2006 5:48 PM ✅Answered}

Bad Idea, as you are then depending upon there being no more than one record saved in any one second interval on a given date.

Better to use a ID field which would be unique - can have an auto incrementing integer field and then create a seperate field to store the create date item,

The Unique ID field can be indexable and doesnt suffer from any pre-concieved assumptions on transaction capacity which would definately be an issue in a multi-user scenario.

If you want to continue this way.   Then getting the current server date is achieved using getdate()    and then you will need to do a cast/convert to generate a string (varchar datatype in SQL) for the appropriate format

T-SQL Cast/Convert
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

I'd probably implement the save method as a stored procedure to wrap this functionality up and simply call the method from VB

_{Friday, June 30, 2006 5:57 PM ✅Answered}

    Dim s As String = Now.ToString("yyMMddhhmmss")
Are you sure you want to do this?  There's no way you can guarantee it will be unique.  Consider an autoincrement field or even a GUID field type...

_{Friday, June 30, 2006 6:04 PM}

Wont this give you the time from the client.   Not the time on the server.

So if the two are the same machine no problems - but if the two are different then they may not be the same, and if you have multiple users accessing the database then the times on the clients may be out of whack (of local time in timezones) and therefore you would run into even more problems.

The time on the server would be a single source and hence a better option.

But I'm totally in agreement that the idea of expecting a date time field to be unique is a real bad idea.

_{Friday, June 30, 2006 10:03 PM}

Thanks very much for the responses and the concern! 

My table in the db has a unique ID field that autoincrements and is set as the primary key.  The number I'm thinking of generating using the date/time function would act as a supplemental tracking number in the app.  This app only allows one user to be logged in at a time, so I'm not too concerned about the possibility of similar numbers being generated.  Maybe I made the wrong choice of words when I said I wanted the number to be "unique."

The advice is much appreciated and will give me a place to start.

Thanks,

Tony

_{Monday, July 3, 2006 10:45 PM}

Your quote

I'm trying to create a function that will generate a unique number based on the date and time a record is saved to a SQL Server database.

So it is dubious if this would ever be unique.   But you have got one idea about generating from the client using simply now and formating a string and using the server time which is via getdate() method and a cast in SQL.

If the SQL server is deployed on the client then there should be no difference in time - if the SQL server is on a different machine then potentional there could be a difference - say different time zones.   So I would edge towards using the server time/date