Date losing value when being stored

Question

I'm trying to get a set of dates into a particular format (ddmmyy) so that they can be run against a number of scripts that I have.

I have managed to convert the date into the correct format, but when I try to store this as a variable it just returns as null or the un-formatted date.

DECLARE @CurrentDate SMALLDATETIME

SELECT @CurrentDate = getdate()
SELECT @CurrentDate = DATEADD(day, -1, @CurrentDate)
SELECT @CurrentDate = STR_REPLACE(CONVERT(varchar,@CurrentDate,3),'/',null)

--Returns this:
20-Mar-2002 00:00:00

DECLARE @CurrentDate SMALLDATETIME

SELECT @CurrentDate = getdate()
SELECT @CurrentDate = DATEADD(day, -1, @CurrentDate)
SELECT STR_REPLACE(CONVERT(varchar,@CurrentDate,3),'/',null)

--Returns this:
020320

I believe the problem comes from the fact that my declared variable is a smalldatetime object but I'm not sure of how to convert it correctly into a string that can be stored as a variable?

I've tried having a second variable and declaring it as a varchar and then storing my date as the varchar but this isn't working either:

DECLARE @CurrentDate SMALLDATETIME
DECLARE @CurrentDateFinal VARCHAR

SELECT @CurrentDate = getdate()
SELECT @CurrentDate = DATEADD(day, -1, @CurrentDate)
SELECT @CurrentDateFinal = CAST(STR_REPLACE(@CurrentDate,'/',null) AS VARCHAR)

--Returns this:
03-Mar-2020 00:00:00

Answer 1

You can do the current date amendment with the dateadd all in one line - there's no need to do two lines. The below gives you the DDMMYY output although I wouldn't use that format personally as you can come unstuck with regional differences (e.g. US prefer MMDDYY and UK tends to be DDMMYY). Also always use 4 digit years IMO.

    DECLARE @FirstDate SMALLDATETIME
    DECLARE @FinalDate varchar(20)

    SELECT @FirstDate = DATEADD(day, -1,getdate())
    set @FinalDate = STR_REPLACE(CONVERT(varchar,@FirstDate,3),'/',null)
    SELECT @FinalDate

    --------------------
    030320
    (1 row affected)