0

Is there a way to set an 8 digit (8 as an example) unique ID in sql which is dependant on the current date and time?

I have seen this on a piece of software that I use and have been told that its possible to generate this in SQL.

Has anyone done this before?

Thanks Pete

Pete
  • 27
  • 1
  • 10
  • What is the purpose of this unique id? – Dan Bracuk Sep 26 '13 at 16:11
  • theres records that I add in tables at certain times during the day. When I need to find all records added at a certain time I can search the first part of the unique ID knowing that all the records with the same 3 characters at the beginning were added around the same sort of time. – Pete Sep 26 '13 at 16:19
  • 2
    This is a poor database design choice. You should never have a primary key field serve more than one purpose, i.e. your primary key should never "mean" anything other than that it uniquely identifies the record. Why not just have a separate field that is a proper date/time field? – rory.ap Sep 26 '13 at 16:28
  • I know its not the best design choice however I am just following suit as this is the way the company have done it and want to continue to do it this way. They do not use the Primary Key for reporting or anything like that....Its just for me so I know that With certain ID's, I know that I inserting them around the same time. I.E if the first two characters were the same I know they were added around the same time. – Pete Sep 27 '13 at 10:00

1 Answers1

0

Perhaps you need a TIMESTAMP typed column.

Finding times can be done in the WHERE clause with the BETWEEN operator and the DATEADD function.

WHERE TimeStampColumn BETWEEN 
DATEADD(ss, -1 * Interval, DesiredTime) AND 
DATEADD(ss,      Interval, DesiredTime)
SMerrill8
  • 528
  • 4
  • 12
  • Using between on datetime columns often causes one to miss records. It it better to user >= the start datetime and < just after the end datetime – Dan Bracuk Sep 26 '13 at 17:57