blocks|key|1150567|text|您的第一个代码片段是将日期时间“底”到月份(将日期设为给定月份的1号，没有时间)+"-1“使其成为前一个月。这段代码中所有多余的不必要的括号让我头疼，下面是等效的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1150568|dateadd(month,datediff(month,0,getdate())-1,0)|code-block|syntax|javascript|1150569|下面是如何将datetime设置为不同单位的方法：|1150570|--Floor+a+datetime
DECLARE+@datetime+datetime;
SET+@datetime+=+'2008-09-17+12:56:53.430';

SELECT+'0+None',++@datetime+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--+none++++2008-09-17+12:56:53.430
UNION+SELECT+'1+Second',DATEADD(second,DATEDIFF(second,'2000-01-01',@datetime),'2000-01-01')++--+Second:+2008-09-17+12:56:53.000
UNION+SELECT+'2+Minute',DATEADD(minute,DATEDIFF(minute,0,@datetime),0)++++++++++++++++++++++++--+Minute:+2008-09-17+12:56:00.000
UNION+SELECT+'3+Hour',++DATEADD(hour,DATEDIFF(hour,0,@datetime),0)++++++++++++++++++++++++++++--+Hour:+++2008-09-17+12:00:00.000
UNION+SELECT+'4+Day',+++DATEADD(day,DATEDIFF(day,0,@datetime),0)++++++++++++++++++++++++++++++--+Day:++++2008-09-17+00:00:00.000
UNION+SELECT+'5+Month',+DATEADD(month,DATEDIFF(month,0,@datetime),0)++++++++++++++++++++++++++--+Month:++2008-09-01+00:00:00.000
UNION+SELECT+'6+Year',++DATEADD(year,DATEDIFF(year,0,@datetime),0)++++++++++++++++++++++++++++--+Year:+++2008-01-01+00:00:00.000
ORDER+BY+1
PRINT'+'
PRINT+'Note+that+when+you+are+flooring+by+the+second,+you+will+often+get+an+arithmetic+overflow+if+you+use+0.+So+pick+a+known+value+that+is+guaranteed+to+be+lower+than+the+datetime+you+are+attempting+to+floor'
PRINT+'this+always+uses+a+date+less+than+the+given+date,+so+there+will+be+no+arithmetic+overflow'
SELECT+'1+Second',DATEADD(second,DATEDIFF(second,DATEADD(day,DATEDIFF(day,0,@datetime),0)-1,@datetime),DATEADD(day,DATEDIFF(day,0,@datetime),0)-1)++--+Second:+2008-09-17+12:56:53.000|1150571|第二个代码片段不会正确地将datetime设置为月份，它只会将日期移到前一个月，并使用与给定datetime相同的日期和时间。|1150572|除此之外，我不确定你真正的要求是什么。|1150573|下面是在OP的第一个代码片段中发生的事情的细目：|1150574|select+convert(datetime,0),'first+datetime+"0"'
select+datediff(month,0,getdate()),+'months+difference+between+the+first+datetime+(1900-01-01)+and+given+"getdate()"'
select+datediff(month,0,getdate())-1,+'months+difference+between+the+first+datetime+(1900-01-01)+and+month+previous+to+given+"getdate()"'
select+dateadd(month,datediff(month,0,getdate())-1,0),+'takes+the+first+datetime+(1900-01-01)+and+adds+1328+months+onto+that'|1150575|输出：|1150576|-----------------------+------------------
1900-01-01+00:00:00.000+first+datetime+"0"

-----------+--------------------------------------------------------------------------------
1329++++++++months+difference+between+the+first+datetime+(1900-01-01)+and+given+"getdate()"

-----------+--------------------------------------------------------------------------------------------------
1328++++++++months+difference+between+the+first+datetime+(1900-01-01)+and+month+previous+to+given+"getdate()"

-----------------------+--------------------------------------------------------------------
2010-09-01+00:00:00.000+takes+the+first+datetime+(1900-01-01)+and+adds+1328+months+onto+that|1150577|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

your first code fragment is "flooring" the datetime to the month (making the date the 1st of the given month with no time) the "-1" makes it the previous month. All the extra unnecessary parenthesis in this code fragment give me a headache, here is the equivalent:

<pre><code>dateadd(month,datediff(month,0,getdate())-1,0)
</code></pre>

This is how to floor a datetime to different units:

<pre><code>--Floor a datetime
DECLARE @datetime datetime;
SET @datetime = '2008-09-17 12:56:53.430';

SELECT '0 None', @datetime -- none 2008-09-17 12:56:53.430
UNION SELECT '1 Second',DATEADD(second,DATEDIFF(second,'2000-01-01',@datetime),'2000-01-01') -- Second: 2008-09-17 12:56:53.000
UNION SELECT '2 Minute',DATEADD(minute,DATEDIFF(minute,0,@datetime),0) -- Minute: 2008-09-17 12:56:00.000
UNION SELECT '3 Hour', DATEADD(hour,DATEDIFF(hour,0,@datetime),0) -- Hour: 2008-09-17 12:00:00.000
UNION SELECT '4 Day', DATEADD(day,DATEDIFF(day,0,@datetime),0) -- Day: 2008-09-17 00:00:00.000
UNION SELECT '5 Month', DATEADD(month,DATEDIFF(month,0,@datetime),0) -- Month: 2008-09-01 00:00:00.000
UNION SELECT '6 Year', DATEADD(year,DATEDIFF(year,0,@datetime),0) -- Year: 2008-01-01 00:00:00.000
ORDER BY 1
PRINT' '
PRINT 'Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor'
PRINT 'this always uses a date less than the given date, so there will be no arithmetic overflow'
SELECT '1 Second',DATEADD(second,DATEDIFF(second,DATEADD(day,DATEDIFF(day,0,@datetime),0)-1,@datetime),DATEADD(day,DATEDIFF(day,0,@datetime),0)-1) -- Second: 2008-09-17 12:56:53.000
</code></pre>

the second code fragment will not properly floor the datetime to the month, it will only move the date to the previous month and use the same day and time as the given datetime.

Beyond that I'm not sure what you are really asking.

here is a breakdown of what is happening in the OP's first code fragment:

<pre><code>select convert(datetime,0),'first datetime "0"'
select datediff(month,0,getdate()), 'months difference between the first datetime (1900-01-01) and given "getdate()"'
select datediff(month,0,getdate())-1, 'months difference between the first datetime (1900-01-01) and month previous to given "getdate()"'
select dateadd(month,datediff(month,0,getdate())-1,0), 'takes the first datetime (1900-01-01) and adds 1328 months onto that'
</code></pre>

OUTPUT:

<pre><code>----------------------- ------------------
1900-01-01 00:00:00.000 first datetime "0"

----------- --------------------------------------------------------------------------------
1329 months difference between the first datetime (1900-01-01) and given "getdate()"

----------- --------------------------------------------------------------------------------------------------
1328 months difference between the first datetime (1900-01-01) and month previous to given "getdate()"

----------------------- --------------------------------------------------------------------
2010-09-01 00:00:00.000 takes the first datetime (1900-01-01) and adds 1328 months onto that
</code></pre>

blocks|key|3262670|text|month,0将其向下舍入到月初。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3262671|如果第一个表达式做的是你想要的，你就不能使用它吗？或者使用第二个表达式，但将已添加的两个GETDATE()切换为0。如果你尝试这样做，你会得到错误吗？|3262672|entityMap^0|0|7|0|18|9|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

<code>month,0</code> makes it round down to the beginning of the month. 

Can't you use your first expression if it does what you want? Or use the second expression but switch the two <code>GETDATE()</code>s you've added to 0. Do you get an error if you try that?

blocks|key|4672907|text|这是我想你可能要找的东西。它是一个SSIS表达式，用于获取给定日期的上个月的第一天(本例中为GETDATE())。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4672908|DATEADD("mm",DATEDIFF("mm",+(DT_DBTIMESTAMP)2,+GETDATE())+-+1,+(DT_DBTIMESTAMP)2)|code-block|syntax|javascript|4672909|我尝试模拟您的SQL版本的表达式，它确定了0日期时间和当前日期时间之间的月数。然后，它将月数添加到0日期时间。|4672910|0日期时间是什么并不是太重要，除了你想要一个月的第一天。在SQL中，0日期时间是1900-01-01+00:00:00.000，因此添加月份会自动得到该月的第一天。在SSIS表达式中，0日期时间是1899-12-30+00:00:00.000。因为您想要一个月的第一天，所以上面的表达式引用2个日期时间。因此，在表达式中，(DT_DBTIMESTAMP)2将数字2转换为1900-01-01+00:00:00.000。|offset|length|style|CODE|4672911|entityMap^0|0|0|0|4H|H|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Here's what I think you may be looking for. It is an SSIS expression that gets the first day of the previous month for a given day (GETDATE() in the example).

<pre><code>DATEADD("mm",DATEDIFF("mm", (DT_DBTIMESTAMP)2, GETDATE()) - 1, (DT_DBTIMESTAMP)2)
</code></pre>

I tried to simulate your SQL version of the expression, which determined the number of months between the 0 datetime and the current datetime. And, then it added the number of months to the 0 datetime.

It's not too important what the 0 datetime is, except that you wanted the 1st day of the month. In SQL the 0 datetime is 1900-01-01 00:00:00.000, so adding months automatically gives you the first day of the month. In SSIS expressions, the 0 datetime is 1899-12-30 00:00:00.000. Since you want the first day of a month, the expression above refers to the 2 datetime. So, in the expression <code>(DT_DBTIMESTAMP)2</code> casts the number 2 to 1900-01-01 00:00:00.000.

blocks|key|4672951|text|我认为在您的派生列中需要的是这个synatx：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4672952|DATEADD("mm",-1,(DT_DBTIMESTAMP)((DT_WSTR,4)DATEPART("YYYY",GETDATE())+%2B+"-"+%2B+(DT_WSTR,2)DATEPART("mm",GETDATE())+%2B+"-01"))+|code-block|syntax|javascript|4672953|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I think what you need in your derived column is this synatx:

<pre><code>DATEADD("mm",-1,(DT_DBTIMESTAMP)((DT_WSTR,4)DATEPART("YYYY",GETDATE()) + "-" + (DT_WSTR,2)DATEPART("mm",GETDATE()) + "-01")) 
</code></pre>

Im having a problem with derived columns in SSIS. When in SSMS i can set a column to have a default value using the following code:

<pre><code>(dateadd(month,datediff(month,(0),getdate())-(1),(0)))
</code></pre>

and when data is entered into the database it will give it the timestamp of the previous month in the following format for example:

<pre><code> 2010-09-01 00:00:00.000
</code></pre>

This strangely is what im looking for and trying to duplicate/produce similar using the Derived Column DFT.

So far i have:

<pre><code>DATEADD("mm",DATEDIFF("mm",GETDATE(),GETDATE()) - 1,GETDATE())
</code></pre>

which produces the month succesfully but the GETDATE() is not a correct replacement for the 0's in the original code.

Does the 0's in the original code signify the start date in SQL?

Any help would be much appreciated.

Regards,

Lee

Using Derived Column to create a date value in the format YYYY-MM-01 00:00:00.000 for each row

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我对SSIS中的派生列有问题。在SSMS中，我可以使用以下代码将列设置为默认值：(dateadd(month,datediff(month,(0),getdate())-(1),(0)))当数据输入到数据库中时，它将以以下格式提供上个月的时间戳，例如： 2010-09-01 00:00:00.000奇怪的是，这就是我正...

问使用派生列为每行创建格式为YYYY-MM-01 00:00:00.000的日期值
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用派生列为每行创建格式为YYYY-MM-01 00:00:00.000的日期值EN