Just curious, Serqiy... why do you prefer the following...

+convert(char(3), replicate('0', 3-len(convert(varchar(3), XID%1000))) + convert(varchar(3), XID%1000)), --The 3 digit numeric part

... over ...

 +REPLACE(STR(XID%1000,3),' ','0'), --The 3 digit numeric part

>the only reason I mentioned 'INSTEAD OF' trigger ...

Got it... thanks for the idea, David.

Cool... thanks for posting back to save us time.

I absolutely agree... especially when they nest such views...

Thanks for the kudo, David.  It's a real pleasure to hear coming from you.

Lowell's idea is a good one... You very well could use the calculation as a computed column.......

Brian,

David is on the right track with a function but, here's some simpler code that uses classic "base" conversion techniques... it may be just a bit faster because it's 100%...

SELECT NEWID()

Use substring to get a piece if you want.

Ami,

I didn't take the time to convert this to your table or column names.  If you are using INT for your ID's, it's good to 727 levels... usually more than...

First, I'd have to ask why do you need a more definitive answer?  The simple truth of the matter is that's the way they made it and none of us...

Farrell,

You didn't read far enough down where I said "Adam Mechanic would probably remind you about the need for auxilary calendar tables or at least a numbers table...".  Or were...

Dunno about all the DST and GMT stuff, but here's a way to find the last Saturday of a month given any date in that month... since it includes time,...

This works...

SELECT CAST(YYYY+MM+DD AS SMALLDATETIME)

The reason why it works is because yyyymmdd is one of the (ISO) recognized datetime formats... no need for the slashes or anything...

I can't take the credit for the following but it does the job for me....

"Normalize 'till it hurts... Denormalize 'till it works."

>This is a presentation issue, hence my second select will give you the output you want, but that is not really a SQL Server job

It depends... if there's no presentation...