Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Problems with Expression Builder in SSIS

    Hi,

    I have a variable that I created in SSIS. This variable name is StartDate and its a datetime data type.

    I want to set this date to eb 12 hours from the current date and hour that we are currently in. I know how to wright this in sql but i am having a hard time finding out how to write it in the expression builder in ssis. Below is the sql that I would use to achive this.

    DECLARE @StartDate DATETIME
    SET @StartDate = DATEADD(HOUR,(DATEDIFF(HOUR,-1,GETDATE())),-1) - '12:00'


    Any help would be greatly appreciated.

    Thank you in advance!

  2. #2
    Join Date
    Jan 2004
    Posts
    164

    Answer

    Hi,

    After spending some time researching I was able to figure out how to get the result that I wanted in the expression builder. To start off I went ahead and created a string variable instead of a datetime variable. Once I did that I used the expression below to give me the result that I needed.

    substring((DT_WSTR, 36)dateadd("hour", -12,getdate()), 1, 13) + ":00:00"

    Thanks to everyone that looked at this. Hope this helps someone else.

    Thanks again,

    Steve

  3. #3
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Thank you for posting your solution.
    Hope this helps someone else.
    Yes, it quite possibly will

  4. #4
    Join Date
    Jan 2004
    Posts
    164
    Quote Originally Posted by papadi View Post
    Thank you for posting your solution.
    Yes, it quite possibly will
    No problem. I'm glad to hear this was helpful. :-)

  5. #5
    Join Date
    Jan 2004
    Posts
    164
    I was also able to figure this out with a datetime variable instead of a string. Below is how this would be done.

    DATEADD("hour", -12, DATEADD("hour",DATEDIFF("hour",(DT_DBTIMESTAMP)0,G ETDATE()),(DT_DBTIMESTAMP)0))


    Thanks,

    Steve

  6. #6
    Join Date
    Aug 2012
    Posts
    30
    Quote Originally Posted by estefex View Post
    I was also able to figure this out with a datetime variable instead of a string. Below is how this would be done.

    DATEADD("hour", -12, DATEADD("hour",DATEDIFF("hour",(DT_DBTIMESTAMP)0,G ETDATE()),(DT_DBTIMESTAMP)0))


    Thanks,

    Steve
    No need of 2 dateadds
    you can reduce it to

    DATEADD("hour",DATEDIFF("hour",(DT_DBTIMESTAMP)0,G ETDATE())-12,(DT_DBTIMESTAMP)0)

  7. #7
    Join Date
    Jan 2004
    Posts
    164
    Thank you for the example. This was very handy. :-)

  8. #8
    Join Date
    Aug 2012
    Posts
    30
    You're welcome

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •