Generating a Unique ID 1

[jw2000]

I would like to be able to generate a Unique ID string (varchar 20) that is based on a datetime field (RecordCreated) and an algorithm.

eg.
2001-08-21 21:08:11.620 -> ef2108g11x620y8a331g082101

where "ef", "g", "x" and "y8a331g" are randomly created letters and or numbers in "ef2108g11x620y8a331g082101"

"2108", "11", "620", "082101" are based on the timestamp, ie. "2108" is the hour and minute, "11" and "620" are the seconds, and "082101" is the MMDDYY

Any suggestions?

 

[vongrunt]

Idea is simple:

- convert date to string(s) easier for manipulation
- get random string
- slice & merge both together

Purely procedural code... nothing fancy:

declare @dt datetime; set @dt = '2001-08-21 21:08:11.620'

declare @datestr char(6), @timestr char(9), @randstr char(11)
set @datestr = replace(convert(char(10), @dt, 10), '-', '')
set @timestr = replace(convert(char(12), @dt, 114), ':', '')
set @randstr = left(replace(newid(), '-', ''), 11)

declare @out varchar(26)

set @out = substring(@randstr, 1, 2) +
	substring(@timestr, 1, 4) +
	substring(@randstr, 3, 1) +
	substring(@timestr, 5, 2) +
	substring(@randstr, 4, 1) +
	substring(@timestr, 7, 3) +
	substring(@randstr, 5, 7) +
	@datestr

select @out

Note: NEWID() "generates" random letters in ranges [0-9, A-F]. If that's not wide enough, use loop and RAND() to get 11 random characters (variable @randstr).

Note #2: this code cannot be written as user-defined function (UDF) because "random" stuff is nondeterministic. If you need reusable code, try stored procedure with OUTPUT parameter instead.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jw2000]

I only have varchar (20) for the Unique ID string. The code currently creates 26 characters. If I change your @out to:

declare @out varchar(20)

Will your code break?

 

[vongrunt]

I copied & pasted your sample output into:

select len('ef2108g11x620y8a331g082101')

... and got 26.

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jw2000]

My mistake ... I created a stored procedure:

CREATE PROCEDURE sp_AddUniqueId
@dt VARCHAR(250)
AS
DECLARE @datestr VARCHAR(6)
DECLARE @timestr VARCHAR(9)
DECLARE @randstr VARCHAR(11)
DECLARE @out VARCHAR(26)

BEGIN

set @datestr = replace(convert(char(10), @dt, 10), '-', '')
set @timestr = replace(convert(char(12), @dt, 114), ':', '')
set @randstr = left(replace(newid(), '-', ''), 11)


set @out = substring(@randstr, 1, 2) +
substring(@timestr, 1, 4) +
substring(@randstr, 3, 1) +
substring(@timestr, 5, 2) +
substring(@randstr, 4, 1) +
substring(@timestr, 7, 3) +
substring(@randstr, 5, 7) +
@datestr

select @out

END
GO



sp_AddUniqueId '2001-08-21 21:08:11.620'
-> 3A2001D-0D8-249DB4DA200108
-> F920016-038-2D9FFA40200108
-> B620015-0F8-2A3B4543200108

Shouldn't it return:

EF2108G11X620Y8A331G082101 ?

where "ef", "g", "x" and "y8a331g" are randomly created letters and or numbers in "ef2108g11x620y8a331g082101"

"2108", "11", "620", "082101" are based on the timestamp, ie. "2108" is the hour and minute, "11" and "620" are the seconds, and "082101" is the MMDDYY

 

[jw2000]

If I use @RecordTimestamp which is the record value of a datetime field in the database:

set @datestr = replace(convert(char(10), cast(@RecordTimestamp as varchar), 10), '-', '')
set @timestr = replace(convert(char(12), cast(@RecordTimestamp as varchar), 114), ':', '')

2003-03-23 10:34:01.513 -> 73Mar A231 20BB4FB09Mar 23
2003-04-08 06:00:07.187 -> 31Apr F 8A 2018DADE9Apr 8


Why are the date values in the MON format instead of MM?

 

[jw2000]

Got it to work! Thank you Vongrunt.