Don't use FORMAT it's slow, and should only be used if you have no choice.

Just use CONVERT with style 112 .

select convert(char(8), @d, 112);

db<>fiddle

Having said that, I'd advise you not to save that into a column. Dates should be kept in date formats, not varchar , until you actually need to query it.

At the most, you could use a computed column

ALTER TABLE YourTable
  ADD FormattedDate AS ( CONVERT(char(8), YourDateColumn, 112) )

You can use the format, but my recommend is to use the DimDate(Table Date) and where clause with date and use dateint in select

Select format(cast(GETDATE() as date),'yyyyMMdd') --20230626

Select format(cast('2023/11/05' as date),'yyyyMMdd') --20231105

I used this code:

select    FORMAT( getdate()  ,'yyyyMMdd')