SQL中如何从指定日期获取去年同日期并保持输入格式？

Got it, let's sort out this formatting issue for you. The problem with your current query is that you're converting the result back to a datetime type—so SQL Server will display it using its default datetime format, not the dd/mm/yyyy style of your input.

To get the output to match '01/04/2016' (which is the dd/mm/yyyy format, corresponding to style 103), you need to convert the calculated date to a varchar string instead of a datetime, specifying style 103.

Here's the adjusted query: #

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, '01/04/2016'), 0)), 103) AS LastYearSameDay

Let's break down what changed: #

• We replaced convert(datetime, ..., 103) with convert(VARCHAR(10), ..., 103): This tells SQL Server to output a string instead of a datetime value. Using VARCHAR(10) is perfect here because dd/mm/yyyy is exactly 10 characters long.
• The core date calculation (DATEADD(YEAR, -1, ...) to get last year's same day) stays the same—we're just changing how we present the final result.

If you're using a variable for the input date (even better practice!), here's how to adapt it: #

DECLARE @InputDate VARCHAR(10) = '01/04/2016'
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, -1, CAST(@InputDate AS DATETIME)), 103) AS LastYearSameDay

Running either of these will give you the output 01/04/2015, which matches your original input format exactly.

内容的提问来源于stack exchange，提问作者ronak6985