Post reply

SSIS Syntax question - Using the HHMM from GETDATE() function

  • August 31, 2016 at 8:19 am

    #316846

    I have the following in the Expression Section for Connection String. It works fine. what it does is it replaces the YYYYMMDD part with the current date.

    Now I plan to have the value "HHMMSS" as part of the file name and expect it to be replaced with the HOUR, MINUTE and SECOND.

    Can you provide me with the syntax for this please ?

    In other words I am asking how to get the HHMMSS part using the (DT_WSTR, 10)(DT_DBDATE)GETDATE() part

    Thx

    @[$Project::DestinationFolder] + REPLACE( @[$Project::Claim_CMSB], "YYYYMMDD",REPLACE((DT_WSTR, 10)(DT_DBDATE)GETDATE(),"-","") )

  • August 31, 2016 at 8:40 am

    #1898566

    You could use the following:

    (DT_WSTR, 6)( (DATEPART( "HH", GETDATE())*10000) + (DATEPART( "MI", GETDATE())*100) + DATEPART( "SS", GETDATE()))

    Or this other option:

    REPLACE( SUBSTRING( (DT_WSTR, 30)GETDATE(), 12, 8), ":", "")

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 31, 2016 at 9:18 am

    #1898582

    Thanks Luis, Works Fine ( See at the very end ) . Instead of replacing HHMMSS, I narrowed it down to replacing only the HH part so the following expression works fine.

    However, I don't like nesting REPLACE inside another REPLACE.

    Is there a way to use a variable in EXPRESSION EDITOR in SSIS and then have 2 or more statements instead on one big nested expression.

    So if I were to write this in T-SQL I would do it as follows:

    Declare @t varchar(500)

    Select @t = @user_variable;

    Select @t=REPLACE( @t, 'HH' , LEFT(CONVERT(CHAR(8), GETDATE(), 112 ),2 );

    Select @t=REPLACE( @t, 'YYYYMMDD' , CONVERT(CHAR(8), GETDATE(), 112 );

    Select @t;

    @[$Project::File_Path] +

    REPLACE(

    (REPLACE( @[$Project::File_name], "YYYYMMDD",REPLACE((DT_WSTR, 10)(DT_DBDATE)GETDATE(),"-","") ))

    ,

    "HH" ,

    (DT_WSTR, 6)( (DATEPART( "HH", GETDATE())*10000))

    )

  • August 31, 2016 at 9:21 am

    #1898583

    I'm curious why you don't like nested REPLACE statements and why you feel that a single large expression would be better than the very simple expressions Luis provided?

  • August 31, 2016 at 9:53 am

    #1898597

    I am not against, but it requires keeping track of parenthesis and commas and syntax.

    Instead if you can ( i am not sure at the moment if there was a way to to that ) write it step by step it would be more neat and could understand easily

    ( I mean easy maintenance )

  • August 31, 2016 at 9:58 am

    #1898600

    Never mind if it is too much work, I figured out by adding 2 more variables. So now my file name has the SERVER_NAME, DB_NAME included as well.

    Still I have the nested REPLACE, I could easily get rid of that if I use a bunch of USER VARIABLES.

    Anyway I am oK for now

    @[$Project::File_Path]

    + @[User::You_Provide_Server_Name]

    + "_"

    + @[User::You_Provide_Database_Name]

    +

    "_"

    +

    REPLACE(

    (REPLACE( @[$Project::File_name], "YYYYMMDD",REPLACE((DT_WSTR, 10)(DT_DBDATE)GETDATE(),"-","") ))

    ,

    "HH" ,

    (DT_WSTR, 6)( (DATEPART( "HH", GETDATE())*10000))

    )

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply