July 24, 2009 at 4:09 am
Hello Everyone,
Just wondering any one has t-sql code to generate fixed digit numbers? For eg. generating for Batch_id, Invoice_number etc.;
declare @i varchar(max)
select @i = SUBSTRING(STR(CAST(CAST(newid() as binary(5))as bigint),12),0,6)
select CAST(@i AS bigint),@i
I have been unsuccessful in using above code because exception occurs on conversion.
ta
July 24, 2009 at 4:32 am
Hi,
try this
declare @i varchar(1000)
select @i = YEAR(getdate())
select CAST(@i AS bigint),@i
July 24, 2009 at 4:56 am
Hi,
Thank you for your script. But I think all the numbers generated on a day will fall in to same value. For eg. If I have to create a 10 batches with the above script it will always yeild me the same value for that day, which might not suit my current requirement.
Ta
July 24, 2009 at 5:03 am
Hi,
use the random values
declare @i int
select @i = (RAND()*100)
select CAST(@i AS bigint),@i
July 24, 2009 at 5:39 am
Hi there,
This will defintely work but some point we might face repeatation. Possible solution is to consider identity column on the table.
Ta
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply