SQL Server Getting First Date of previous year

tcbh2hod 于 2023-04-28 发布在其他

关注(0)|答案(5)|浏览(208)

how can I modify the statement below to get first date of previous year (Preferably without introducing additional quotes)

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), -1)

5条答案

If you use SQL Server 2012+ use DATEFROMPARTS:

SELECT DATEFROMPARTS ( DATEPART(yyyy, GETDATE()) - 1, 1, 1 )

got it to work

DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

The better answer is:

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)

If you understand how the original works, you know where to put the -1.

The arguments for DATEADD are DATEADD(interval, increment int, expression smalldatetime).

So in the expression above, the DATEDIFF function returns an integer, being the difference in years between the current date and date zero (0).

The expression in the above statement accepts a zero (0) as a smalldatetime.

So it adds the number of years between "date zero" and the "current date" to date zero.

Date zero is 1 Jan 1900, so that will give you 1 Jan for whatever the provided date is.

The following gives you the zero date:

select dateadd(yy,0,0)

So to go back one more year, you simply subtract 1 from the interval, so the interval becomes:

DATEDIFF(yy,0,GETDATE())-1

Another method is:

select dateadd(year, -1, datename(year, getdate()) + '0101')

Use this: SELECT DATEFROMPARTS(YEAR(GETDATE())-1,1,1)