Search code examples
sqlformatsubtractiongetdate

SQL Convert Date format and Subtract days

I am trying to format GETDATE() '08/10/2021' where it subtracts 9 days from it however I keep getting '2021-09-30 00:00:00.000' with trailing 0...where all I want is just the date in this format '08/10/2021'

I'm using 'select convert(nvarchar(MAX), getdate(), 103)' to return the date in the format I want but I need to be able to subtract 9 days from this.

The thing is when I try 'SELECT CONVERT(VarChar(20), DATEADD(DD, -9, GETDATE()), 103)' (which works) in my query it errors with:

'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'

Solution

  • this works:

    SELECT CONVERT(VarChar(10), DATEADD(DD, -9, GETDATE()), 103)