October 11, 2011 at 7:49 am
Hi,
I am trying to create expressions for datetime variables in ssis. I need one for yesterday midnight and other one for one before yesterday midnight. I have taken the following expressions but not sure how it is correct:
Yesterday midnight:
RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ),
2) + "/"+RIGHT("0" + (DT_STR, 2, 1252) DAY( DATEADD("dd",-1,GETDATE()) ), 2) +"/"+ (DT_STR, 4,
1252) YEAR( GETDATE() )+" 00:00:00 AM"
If today is 10/11/2011, it is giving: 10/10/2011 00:00:00 AM
Day before yesterday midnight:
RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ),
2) + "/"+RIGHT("0" + (DT_STR, 2, 1252) DAY( DATEADD("dd",-1,GETDATE()) ), 2) +"/"+ (DT_STR, 4,
1252) YEAR( GETDATE() )+" 11:59:59 PM"
If today is 10/11/2011, it is giving: 10/10/2011 11:59:59 PM
Please confirm this.
Thank
October 11, 2011 at 8:03 am
What about if I change the above to
Yesterday midnight:
DATEADD("Day", -1,(DT_DBDATE)DATEDIFF("Day", (DT_DBDATE) 0, GETDATE()))
: 10/10/2011 12:00:00 AM
Day before yesterday midnight:
DATEADD("Day", -2,(DT_DBDATE)DATEDIFF("Day", (DT_DBDATE) 0, GETDATE()))
:10/9/2011 12:00:00 AM
Thanks
June 19, 2017 at 11:07 am
(have same question. Since this is old post, deleted and made new one at https://www.sqlservercentral.com/Forums/1882132/Expression-needed-for-Midnight-of-previous-day)
--Quote me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply