Passing a parameter inside a statement

  • Hi there, need some help on this:

    within a SSIS package in a SQL task:
    I want to deIete all records in a table, from a certain date, this date is stored in the table as YYYYMMDD,

     I can achieve this by  Delete  From

      where DateID >=  (Convert (char(8) , '2018-10-01 00:00:00', 112))

    But I have to pass the DateTime value as a parameter, so I tried:

    Delete  From

      where DateID >=  (Convert (char(8) ,?, 112))

    The Questionmark recieves the correct DateTime, but it doesn't work this way

      I understand why this doesn't work, but I am looking for the correct way to build this statement?

    Help would be very nice

    Thx so far

  • sinds1962 - Saturday, November 24, 2018 3:30 AM

    Hi there, need some help on this:

    within a SSIS package in a SQL task:
    I want to deIete all records in a table, from a certain date, this date is stored in the table as YYYYMMDD,

     I can achieve this by  Delete  From

      where DateID >=  (Convert (char(8) , '2018-10-01 00:00:00', 112))

    But I have to pass the DateTime value as a parameter, so I tried:

    Delete  From

      where DateID >=  (Convert (char(8) ,?, 112))

    The Questionmark recieves the correct DateTime, but it doesn't work this way

      I understand why this doesn't work, but I am looking for the correct way to build this statement?

    Help would be very nice

    Thx so far

    What is the data type of the DateID column?
    😎

  • Hi Eirikur

    The data type of dateID is varchar (8) 
    This field is filled with the same statement (Convert (char(8) , 'DateTime',112)), this is no issue with date-format, but in the correct way of passing the value of the parameter through to the statement

  • sinds1962 - Saturday, November 24, 2018 3:30 AM

    Hi there, need some help on this:

    within a SSIS package in a SQL task:
    I want to deIete all records in a table, from a certain date, this date is stored in the table as YYYYMMDD,

     I can achieve this by  Delete  From

      where DateID >=  (Convert (char(8) , '2018-10-01 00:00:00', 112))

    But I have to pass the DateTime value as a parameter, so I tried:

    Delete  From

      where DateID >=  (Convert (char(8) ,?, 112))

    The Questionmark recieves the correct DateTime, but it doesn't work this way

      I understand why this doesn't work, but I am looking for the correct way to build this statement?

    Help would be very nice

    Thx so far

    you said you understand why - but you didn't tell us why it is failing.

    As long as the parameter is being supplied correctly the statement should work - but if the value is not correctly passed it wont.

    another  option is - I normally prefer this one 

    declare @inputvalue datetime = ?
    /*
    in here insert the parameter values onto a debug table while testing to ensure they are indeed being supplied correctly
    */
    Delete  From

      where DateID >=  (Convert (char(8) ,@inputvalue, 112))

    on another note that DateID should probably be a int, not a char(8)

    EDIT: the ? will work for oledb - for ado.net it uses a named parameter

  • Your query (below) is in the right format:
    Delete From

    where DateID >= (Convert (char(8) ,?, 112))
    If, however, you are storing your DateID as a varchar, why not pass a varchar? Get SSIS to use the varchar value in the format yyyyMMdd and then pass that simply as:
    DELETE FROM [Table] WHERE DateID >= ?;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • sinds1962 - Saturday, November 24, 2018 3:59 AM

    The data type of dateID is varchar (8)

    {shiver}

    VARCHAR carries an extra 2 bytes of overhead to remember the size of the data.  Since you're using a fixed formatted representation of the date as YYYYMMDD {shiver} that will never be larger than 8 characters and usually never be smaller than 8 characters, you "shou use" CHAR(8) for the column data type IF you can't make any other change.

    I say "should use" in quotes because I'd only store character based dates if I were sending a file (perhaps not even then) and then only long enough to write to the file.

    Instead, I'd use either DATE or DATETIME with a heavy preference for the latter because of all the cool stuff that can easily be done with it but can't be done with DATE directly.

    DATE, of course, has an advantage of forcing only whole dates and only requires 3 bytes.

    My recommendation is to keep the presentation layer separate from the data layer and don't store formatted dates in your database tables.  Staging tables?  Maybe.  But not in permanent tables.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thom A - Saturday, November 24, 2018 5:35 AM

    Your query (below) is in the right format:
    Delete From

    where DateID >= (Convert (char(8) ,?, 112))
    If, however, you are storing your DateID as a varchar, why not pass a varchar? Get SSIS to use the varchar value in the format yyyyMMdd and then pass that simply as:
    DELETE FROM [Table] WHERE DateID >= ?;

    Hi Thom

    Thx! didn't  thought about this, but this must be the most simple sollution

  • Jeff Moden - Saturday, November 24, 2018 11:24 AM

    sinds1962 - Saturday, November 24, 2018 3:59 AM

    The data type of dateID is varchar (8)

    {shiver}

    VARCHAR carries an extra 2 bytes of overhead to remember the size of the data.  Since you're using a fixed formatted representation of the date as YYYYMMDD {shiver} that will never be larger than 8 characters and usually never be smaller than 8 characters, you "shou use" CHAR(8) for the column data type IF you can't make any other change.

    I say "should use" in quotes because I'd only store character based dates if I were sending a file (perhaps not even then) and then only long enough to write to the file.

    Instead, I'd use either DATE or DATETIME with a heavy preference for the latter because of all the cool stuff that can easily be done with it but can't be done with DATE directly.

    DATE, of course, has an advantage of forcing only whole dates and only requires 3 bytes.

    My recommendation is to keep the presentation layer separate from the data layer and don't store formatted dates in your database tables.  Staging tables?  Maybe.  But not in permanent tables.

    Hi Jeff
    I get the point you make, and surely you are right, i will remember this
    But for this case I will use de DateID as key in a dimensional Time-Table, therefore this format is more suitable for me

  • sinds1962 - Sunday, November 25, 2018 2:28 AM

    Jeff Moden - Saturday, November 24, 2018 11:24 AM

    sinds1962 - Saturday, November 24, 2018 3:59 AM

    The data type of dateID is varchar (8)

    {shiver}

    VARCHAR carries an extra 2 bytes of overhead to remember the size of the data.  Since you're using a fixed formatted representation of the date as YYYYMMDD {shiver} that will never be larger than 8 characters and usually never be smaller than 8 characters, you "shou use" CHAR(8) for the column data type IF you can't make any other change.

    I say "should use" in quotes because I'd only store character based dates if I were sending a file (perhaps not even then) and then only long enough to write to the file.

    Instead, I'd use either DATE or DATETIME with a heavy preference for the latter because of all the cool stuff that can easily be done with it but can't be done with DATE directly.

    DATE, of course, has an advantage of forcing only whole dates and only requires 3 bytes.

    My recommendation is to keep the presentation layer separate from the data layer and don't store formatted dates in your database tables.  Staging tables?  Maybe.  But not in permanent tables.

    Hi Jeff
    I get the point you make, and surely you are right, i will remember this
    But for this case I will use de DateID as key in a dimensional Time-Table, therefore this format is more suitable for me

    Yep... I know... but you're making a mistake.  DateID shouldn't be a VARCHAR or a CHAR, especially not for a dimensional Time-Table.  At the worst, it should be an INT.  Certainly NOT a VARCHAR(anything).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Sunday, November 25, 2018 7:47 AM

    Yep... I know... but you're making a mistake.  DateID shouldn't be a VARCHAR or a CHAR, especially not for a dimensional Time-Table.  At the worst, it should be an INT.  Certainly NOT a VARCHAR(anything).

    Must admit that I haven't recently looked at the difference between using date and an integer in the tabular model, integers will conform well in the VertiPaq engine, not certain how the big-endian byte order of the date data type affects this.
    😎

  • If you don't use a date in tabular model, you can't mark the column as a date, so you can't use a lot of the built in time intelligence functions... so there's that.

  • pietlinden - Sunday, November 25, 2018 9:49 AM

    If you don't use a date in tabular model, you can't mark the column as a date, so you can't use a lot of the built in time intelligence functions... so there's that.

    Good point, the date/time logic will of course not be available including all the period on period etc.
    😎

  • Eirikur Eiriksson - Sunday, November 25, 2018 9:56 AM

    pietlinden - Sunday, November 25, 2018 9:49 AM

    If you don't use a date in tabular model, you can't mark the column as a date, so you can't use a lot of the built in time intelligence functions... so there's that.

    Good point, the date/time logic will of course not be available including all the period on period etc.
    😎

    Kind of good point - but not always.

    As in part of the cases there are date/time dimensions, on those the logic is covered even if the link between the fact and the dimensions is a Int

Viewing 13 posts - 1 through 12 (of 12 total)

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