November 24, 2018 at 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
November 24, 2018 at 3:42 am
sinds1962 - Saturday, November 24, 2018 3:30 AMHi 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?
😎
November 24, 2018 at 3:59 am
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
November 24, 2018 at 4:23 am
sinds1962 - Saturday, November 24, 2018 3:30 AMHi 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
November 24, 2018 at 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 >= ?;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 24, 2018 at 11:24 am
{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
Change is inevitable... Change for the better is not.
November 25, 2018 at 2:25 am
Thom A - Saturday, November 24, 2018 5:35 AMYour query (below) is in the right format:Delete Fromwhere 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
November 25, 2018 at 2:28 am
sinds1962 - Saturday, November 24, 2018 3:59 AMThe 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
November 25, 2018 at 7:47 am
Jeff Moden - Saturday, November 24, 2018 11:24 AMsinds1962 - Saturday, November 24, 2018 3:59 AMThe 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
Change is inevitable... Change for the better is not.
November 25, 2018 at 8:09 am
Jeff Moden - Sunday, November 25, 2018 7:47 AMYep... 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.
😎
November 25, 2018 at 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.
November 25, 2018 at 9:56 am
pietlinden - Sunday, November 25, 2018 9:49 AMIf 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.
😎
November 25, 2018 at 10:59 am
Eirikur Eiriksson - Sunday, November 25, 2018 9:56 AMpietlinden - Sunday, November 25, 2018 9:49 AMIf 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