How to change Date format for a date field

Can someone let me know what Data Type will remove the slashes from a date field?

For example, the as_of_date field is formatted as 31/05/2023. Is there format that will remove the slashes such that the date looks as follows 31052023 or 20230531

I have tried VARCHAR, but that just formats the date as 2023-05-31.

Any thoughts?

This is where you can find the documentation:

CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn

If the column is actually defined as a DATE data type - then it isn't formatted. It is stored as an integer offset from the zero date which is 0001-01-01.

The format you see in SSMS is defined by the regional settings in Windows. The format you are showing tells me you have windows set to use a regional setting that defines dates as DD/YY/MMMM.

Now - if the 'field' is actually storing the data as a string then you don't actually have a date. What you have is a string that looks like it might be a date.