Expression for yesterday midnight

  • 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

  • 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

  • (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