I am trying to get the specific day of a Year.
我想要获得一年中的具体日子。
Here's what I have tried till now:-
这是我到现在为止所尝试的: -
-- Declare few variables
DECLARE @Currentdate AS DATETIME
DECLARE @DueDate AS DATETIME
DECLARE @NewDate AS DATETIME
-- Set the variables properly, just for testing
SET @Currentdate = GETDATE()
SET @DueDate = DATEADD(MONTH, 2, DATEADD(YEAR, 1, @Currentdate))
-- Check the output
SELECT @Currentdate -- 2013-09-30 00:00:00.000
SELECT @DueDate -- 2014-11-30 00:00:00.000
So, I want to get the @NewDate
based on the @Currentdate
year. For this I tried:-
所以,我想根据@Currentdate年份获得@NewDate。为此,我试过: -
SELECT @NewDate = DATEADD(DAY, DAY(DATEDIFF(day, 1, @DueDate)), DATEADD(MONTH, DATEDIFF(MONTH, 0, @Currentdate), 0))
SELECT @NewDate -- 2013-09-30 00:00:00.000
But it didn't worked. :(
但它没有奏效。 :(
My expected result is like:
我的预期结果如下:
-- 2013-11-30 00:00:00.000
-- Having the due date month and date same, but the year as current date one.
Any help is appreciated!
任何帮助表示赞赏!
UPDATE
Sorry for all the confusion I have created. My question in simple words is:-
对不起我创造的所有困惑。用简单的话说我的问题是: -
I want to get the a new date variable having the date and the month same as @DueDate
variable but the year as given in the @Currentdate
variable.
我想得到一个新的日期变量,其日期和月份与@DueDate变量相同,但是@Currentdate变量中给出的年份。
I hope that would clear things up a bit.
我希望这会让事情变得清晰起来。
3 个解决方案
#1
3
If the question is "given I have a particular datetime value in one variable, can I set another variable to be for the same day and month but in the current year" then the answer would be:
如果问题是“给定我在一个变量中有一个特定的日期时间值,我可以将另一个变量设置为同一天和月但在当前年份”,那么答案将是:
declare @DueDate datetime
declare @NewDate datetime
set @DueDate = '20141130'
--Need to set @NewDate to the same month and day in the current year
set @NewDate = DATEADD(year,
--Here's how you work out the offset
DATEPART(year,CURRENT_TIMESTAMP) - DATEPART(year,@DueDate),
@DueDate)
select @DueDate,@NewDate
I want to get the a new date variable having the date and the month same as @DueDate variable but the year as given in the @Currentdate variable.
我想得到一个新的日期变量,其日期和月份与@DueDate变量相同,但是@Currentdate变量中给出的年份。
Well, that's simply the above query with a single tweak:
好吧,这只是上面的查询,只需一个调整:
set @NewDate = DATEADD(year,
--Here's how you work out the offset
DATEPART(year,@Currentdate) - DATEPART(year,@DueDate),
@DueDate)
#2
0
Try this instead ?
试试这个呢?
DECLARE @Currentdate AS DATETIME
DECLARE @DueDate AS DATETIME
-- Set the variables properly, just for testing
SET @Currentdate = GETDATE()
SET @DueDate = DATEADD(MONTH, 2, DATEADD(YEAR, 1,
DateAdd(day, datediff(day, 0, @currentDate), 0)))
-- Check the output
SELECT @Currentdate -- 2013-09-30 18:32:35.310
SELECT @DueDate
Using DateAdd(day, datediff(day, 0, @DateTime), 0)
strips off the time portion. You should also check out this SO Question/answer.
使用DateAdd(day,datediff(day,0,@ DateTime),0)剥离时间部分。您还应该查看此SO问题/答案。
#3
0
Try this one:
试试这个:
CAST(CAST( -- cast INT to VARCHAR and then to DATE
YEAR(GETDATE()) * 10000 + MONTH(@DueDate) * 100 + DAY(@DueDate) -- convert current year + @DueDate's month and year parts to YYYYMMDD integer representation
+ CASE -- change 29th of February to 28th if current year is a non-leap year
WHEN MONTH(@DueDate) = 2 AND DAY(@DueDate) = 29 AND ((YEAR(GETDATE()) % 4 = 0 AND YEAR(GETDATE()) % 100 <> 0) OR YEAR(GETDATE()) % 400 = 0) THEN 0
ELSE -1
END
AS VARCHAR(8)) AS DATE)
#1
3
If the question is "given I have a particular datetime value in one variable, can I set another variable to be for the same day and month but in the current year" then the answer would be:
如果问题是“给定我在一个变量中有一个特定的日期时间值,我可以将另一个变量设置为同一天和月但在当前年份”,那么答案将是:
declare @DueDate datetime
declare @NewDate datetime
set @DueDate = '20141130'
--Need to set @NewDate to the same month and day in the current year
set @NewDate = DATEADD(year,
--Here's how you work out the offset
DATEPART(year,CURRENT_TIMESTAMP) - DATEPART(year,@DueDate),
@DueDate)
select @DueDate,@NewDate
I want to get the a new date variable having the date and the month same as @DueDate variable but the year as given in the @Currentdate variable.
我想得到一个新的日期变量,其日期和月份与@DueDate变量相同,但是@Currentdate变量中给出的年份。
Well, that's simply the above query with a single tweak:
好吧,这只是上面的查询,只需一个调整:
set @NewDate = DATEADD(year,
--Here's how you work out the offset
DATEPART(year,@Currentdate) - DATEPART(year,@DueDate),
@DueDate)
#2
0
Try this instead ?
试试这个呢?
DECLARE @Currentdate AS DATETIME
DECLARE @DueDate AS DATETIME
-- Set the variables properly, just for testing
SET @Currentdate = GETDATE()
SET @DueDate = DATEADD(MONTH, 2, DATEADD(YEAR, 1,
DateAdd(day, datediff(day, 0, @currentDate), 0)))
-- Check the output
SELECT @Currentdate -- 2013-09-30 18:32:35.310
SELECT @DueDate
Using DateAdd(day, datediff(day, 0, @DateTime), 0)
strips off the time portion. You should also check out this SO Question/answer.
使用DateAdd(day,datediff(day,0,@ DateTime),0)剥离时间部分。您还应该查看此SO问题/答案。
#3
0
Try this one:
试试这个:
CAST(CAST( -- cast INT to VARCHAR and then to DATE
YEAR(GETDATE()) * 10000 + MONTH(@DueDate) * 100 + DAY(@DueDate) -- convert current year + @DueDate's month and year parts to YYYYMMDD integer representation
+ CASE -- change 29th of February to 28th if current year is a non-leap year
WHEN MONTH(@DueDate) = 2 AND DAY(@DueDate) = 29 AND ((YEAR(GETDATE()) % 4 = 0 AND YEAR(GETDATE()) % 100 <> 0) OR YEAR(GETDATE()) % 400 = 0) THEN 0
ELSE -1
END
AS VARCHAR(8)) AS DATE)