如何通过考虑现有日期来获取新日期 [英] How to get a new date by considering the existing date

问题描述

我有一个表，该表的列[DueDate]的DateTime数据类型带有一些值.
我想要一个具有DateTime数据类型的新列[InstDueDate]，
[InstDueDate]的值应为[day(DueDate)-Jul-2012]
(ie)[day(DueDate)]-[Current Month]-[Current year]

例如:

Hi,
Iam having a table with column [DueDate] with DateTime datatype with some values.
I want a new column [InstDueDate] with DateTime datatype,
the value of [InstDueDate] should be [day(DueDate)-Jul-2012]
(ie)[day(DueDate)]-[Current Month]-[Current year]

For Example:

<br />
DueDate InstDueDate<br />
31-Mar-2004 31-Jul-2012<br />
30-May-2008 30-Jul-2012<br />
26-Dec-2003 26-Jul-2012<br />
25-Apr-2012 25-Jul-2012

对于每个月，我必须更新[InstDueDate]列.
对于有30天的月份，我们必须考虑[DueDate]列
9月[2004年3月31日]的月份应该是[2012年9月30日]
因为在9月中不存在31.

For each month i have to update [InstDueDate] column.
For the month having 30 days we have to consider the [DueDate] column
for the month of september [31-Mar-2004] should be [30-Sep-2012]
because 31 does not exist in september month.

推荐答案

在这种情况下，我需要更新查询

I need a update query for this scenario

看看 DATEDIFF和DATEADD [

请检查以下内容.

Please check the below one.

DECLARE @Month nvarchar(2)
DECLARE @Year nvarchar(4)
Declare @MaxDay int

SET @Month = '7'
SET @Year = '2012'
Select @MaxDay=Day(DateAdd(d, -1, DateAdd(m, 1, @Year+ '-' + (@Month) + '-1 23:59:59.998')))


select  Case when DAY(Duedate)<=@MaxDay Then Cast(DAY(Duedate) as varchar) +'-'+  Substring(DATENAME(m, '1900-' + CAST(@Month as varchar(2)) + '-1'),1,3) +'-'+@Year
else  Cast(@MaxDay as varchar) + +'-'+Substring(DATENAME(m, '1900-' + CAST(@Month as varchar(2)) + '-1'),1,3) +'-'+@Year

  end from datemagics

在这里，我的表名称是datemagics，而Duedate是我的列.您可以用表名和列名替换

here my table name is datemagics and Duedate is my column. You can replace with your table and column name

这篇关于如何通过考虑现有日期来获取新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！