Convert date stored as Decimal (9, 0) and subtract a year from the date-CodePudding

I am trying to convert a SQL date which is stored as a Decimal (9,0) to a date field so I can then use datediff to subtract a year.

I have tried several ways. So far I have been able to cast to a date, but I can't get Datediff to work with this. Can anyone help please?

Here is what I have which converts to a date field: Format(cast(cast([DOCDATE] as varchar(10)) as date), 'dd/MM/yyyy')

When I try using datediff with this DateDiff(year, -1, (Format(cast(cast([DOCDATE] as varchar(10)) as date), 'dd/MM/yyyy'))) - it results in error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

I have tried a variety of combinations of Convert and Cast, but I'm failing each time.

I would really appreciate some help if someone can show me how to do this.

CodePudding user response：

You aren't quite using the right functions. I'm not sure how your data looks, but this working example might help you fix your problem.

Return the date minus a year as a date:

select dateadd(year, -1, cast(cast(20220822 as varchar(10)) as date))

Return the date minus a year as a varchar that looks like a date:

select format(dateadd(year, -1, cast(cast(20220822 as varchar(10)) as date)), 'dd/MM/yyyy')