Why rand() in format function returns null?-CodePudding

I want to generate a 6 digit number in SQL Server, which first digit is allowed to be zero.

For generating a random 6 digit number I use this code:

round(rand()*power(10,6),0)

For making sure that it is a 6 character number I use as this example:

format(12345,'D6')

Which returns:

But when I use below phrase it returns NULL

select format(round(rand()*power(10,6),0),'D6')

I was searching to find the cause, but I just understand that even format(rand()*power(10,6),'D6') returns null, while format(round(power(10,3),0),'D6') and select format(power(10,3),'D6') returns the answer. It shows that the problem is neither about power() nor round(). The rand() function is the cause.

I use this code to solve my problem:

declare @num int = round(rand()*power(10,6),0)

select format(@num,'D6')

But I just want to know why rand() in format() returns null, I couldn't find the cause.

If you have any idea, I'll appreciate it. Thanks.

CodePudding user response：

The function rand()*power(10,6) returns a float. When you put it into a variable type int you force the conversion to int.
We can do the same thing with cast(.. as int) as in select format(cast(rand()*power(10,6)as int),'D6') which works correctly. (As the value is already a whole number there is no need to use round())
If we use a variable type float we get a null value:

declare @num float = round(rand()*power(10,6),0)    
select format(@num,'D6')

| (No column name) |
| :--------------- |
| null             |

The problem therefore occurs when we use format() with a float as argument.