Search code examples
sqldatecastingdecimaldatediff

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

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.

Solution

  • 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')