如何查找上一年的日期 [英] How to find previous year dates

问题描述

在sql中如何查找上一年的日期
我有前一年的工作日，月，周，年，我已经从
中计算出了这些数字 当前的日期和日期，问题是如何查找同一天，同一年和同一月的上一年


例子

2011年1月1日-> 2012-01-01

建议我如何实现上一年的日期

In sql how i can find previous year dates
I have previous year weekday,month,week,year, I have calculated these figures from the
current from and to date, The Question is how to find the previous year similar same day,year and month


Example

2011-01-01 -> 2012-01-01

suggest me how to achive this previous year dates

推荐答案

使用 DATEADD [ ^ ]函数-与负值或正值.

Use the DATEADD[^] function - it works with a negative or positive value.

我刚刚创建了一个可以满足您需要的函数

I just created a function which does what you need

alter FUNCTION fnBala (
	@Year varchar(20),
	@Month varchar(20),
	@Day2Find varchar(20),
	@nth int
)
RETURNS date
AS
BEGIN
	
	-- select dbo.fnBala (''2012'',''JAN'',''MON'',1)
	Declare @StartDate date =convert(date,''1/''+@Month+''/''+@Year)
	Declare @FirstDateOfMonth date
	
	Declare @Factor int=-1

	
	if @Day2Find=''SUN'' 		set @Factor=6
	else if @Day2Find=''SAT''		set @Factor=5
	else if @Day2Find=''MON''		set @Factor=0
	else if @Day2Find=''TUE''		set @Factor=1
	else if @Day2Find=''WED''		set @Factor=2
	else if @Day2Find=''THU''		set @Factor=3
	else if @Day2Find=''FRI''		set @Factor=4
	
		
	if  ( @Factor - DATEDIFF(DD,0,@StartDate)%7) <0
		set @FirstDateOfMonth=dateadd("d",( @Factor - DATEDIFF(DD,0,@StartDate)%7)+7,@StartDate)	
	else		
		set @FirstDateOfMonth=dateadd("d",( @Factor - DATEDIFF(DD,0,@StartDate)%7),@StartDate)
	
	return dateadd("d",7*(@nth-1),@FirstDateOfMonth)
	
END

创建函数

Fist time when you are creating the function

alter FUNCTION fnBala

时的第一时间需要由

create FUNCTION fnBala 

代替

1如果要查找feb2010月份1月1日是星期几?

1 If you want to find if feb2010 month 1st sunday is what date?

select dbo.fnBala (''2010'',''FEB'',''SUN'',1)

2.如果要查找feb2011第3个星期日是星期几?

2. If you want to find feb2011 3rd sunday is what date?

select dbo.fnBala (''2011'',''FEB'',''SUN'',3)

我刚刚在SQL Server 2008上对其进行了测试，它对我有用，并且不确定您的SQL Server版本是什么，请告诉我您是否随SQL Server版本一起收到任何错误消息


希望这对您有帮助

I just tested this on SQL server 2008 and it works for me and not suree what is your SQL server version and please let me know if you get any error message along with the SQL server version


Hope this helps

据我了解，您想例如在2010年2月知道第一个星期日的日期.
所以这是一种解决方案:

As I understood your question you want for example to know in Feb 2010 what is the date of first Sunday.
So here is one solution :

declare @y int
set @y = 2010  --in which year we are interested ?
declare @m int
set @m = 2 --February 
declare @d int
set @d = 1  -- start from first day of month
declare @dp int
set @dp = 0  --this is an invalid day of the week ! untill we reach to desired one

--finding first sunday in Feb 2010
set @dp = datepart(dw,	CAST(CAST(@y AS varchar) + '-' + CAST(@m AS varchar) + '-' + CAST(@d AS varchar) AS DATETIME) )
while @dp!=1 --search for sunday
begin
	set @d = @d+1	
	set @dp = datepart(dw,	CAST(CAST(@y AS varchar) + '-' + CAST(@m AS varchar) + '-' + CAST(@d AS varchar) AS DATETIME) )
end
print CAST(CAST(@y AS varchar) + '-' + CAST(@m AS varchar) + '-' + CAST(@d AS varchar) AS DATETIME)

它需要一些自定义设置来满足您的需求，但是我敢肯定您可以处理.

如果解决了您的问题或投了赞成票，请标记为答案:)

It need some customizations to meet your needs but I''m sure you can handle that.

Please mark it as an answer if it solved your problem or vote it up :)

这篇关于如何查找上一年的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！