|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to get the record created date?How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if parameter startdate is known: DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180 Thanks in advance. -tc You don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime) And 'timestamp' as the timestamp datatype has nothing to do with TIME. -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "tcw" <tcwa***@msn.com> wrote in message news:%23yZt9I4pGHA.4912@TK2MSFTNGP05.phx.gbl... > How do I delete rows that are older than 180 days if the table does not > contain a column of timestamp? The following statement works fine if > parameter startdate is known: > > DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180 > > Thanks in advance. > > -tc > > (Every table 'should' have such a column: RecordCreated datetime) Whoa, that's a pretty broad generalization. Such a column is useful, but in most applications it isn't necessary on every single table. A Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of course, not needed at all in some databases. But for general business data ... -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:epyLdq4pGHA.4812@TK2MSFTNGP04.phx.gbl... >> (Every table 'should' have such a column: RecordCreated datetime) > > Whoa, that's a pretty broad generalization. Such a column is useful, but > in most applications it isn't necessary on every single table. > > A > It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you encourage efficiency. -- Show quoteBest regards Mark Baldwin "Arnie Rowland" <ar***@1568.com> wrote in message news:eiBkZz4pGHA.4912@TK2MSFTNGP05.phx.gbl... > Very True, but I prefer to drive home the point for the generalization, > and then once the idea has taken hold, start dealing with the exceptions. > And of course, not needed at all in some databases. > > But for general business data ... > > -- > Arnie Rowland > "To be successful, your heart must accompany your knowledge." > > > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:epyLdq4pGHA.4812@TK2MSFTNGP04.phx.gbl... >>> (Every table 'should' have such a column: RecordCreated datetime) >> >> Whoa, that's a pretty broad generalization. Such a column is useful, but >> in most applications it isn't necessary on every single table. >> >> A >> > > I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment of large corporation and government agencies that I work with, but for the last several years, all projects I've worked with have either required or benefited from having not only a date column (entered/changed) -but also a column capturing 'who' was responsible. And updates/deletes from selected tables may be shadowed to an archive table/server. I tend to work with VLDB's that must meet requirements set out by Aegis (law enforcement), SarBox (financial), and/or HIPPA (medical). Personally, I think that business systems 'should' have date and person columns on almost every table. They solve so many of the 'problems' that come up over a project's lifecycle. -Such as the one posted by the OP. -- Show quoteArnie Rowland "To be successful, your heart must accompany your knowledge." "Mark" <swo***@hotmail.com> wrote in message news:OT$O7e$pGHA.5064@TK2MSFTNGP05.phx.gbl... > It would be better to drive home the fact that each row should take up as > little space as possible and then deal with the exceptions. That way you > encourage efficiency. > > -- > Best regards > Mark Baldwin > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:eiBkZz4pGHA.4912@TK2MSFTNGP05.phx.gbl... >> Very True, but I prefer to drive home the point for the generalization, >> and then once the idea has taken hold, start dealing with the exceptions. >> And of course, not needed at all in some databases. >> >> But for general business data ... >> >> -- >> Arnie Rowland >> "To be successful, your heart must accompany your knowledge." >> >> >> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in >> message news:epyLdq4pGHA.4812@TK2MSFTNGP04.phx.gbl... >>>> (Every table 'should' have such a column: RecordCreated datetime) >>> >>> Whoa, that's a pretty broad generalization. Such a column is useful, >>> but in most applications it isn't necessary on every single table. >>> >>> A >>> >> >> > > > benefited from having not only a date column (entered/changed) -but also That kind of data, imho, belongs in a separate auditing table.> a column capturing 'who' was responsible. Otherwise, you are forced to either have really wide tables or only track the LAST person who changed it. > Personally, I think that business systems 'should' have date and person I think for higher level entities that's not a bad idea. For complex OLTP > columns on almost every table. systems with hundreds of tables, you're going to drown yourself in information overload. A SQL Server does not track this information, so you're going to have to add a
column like: ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP; Of course, you won't be able to take advantage of the values for this specifc task until 6 more months have passed. A Show quote "tcw" <tcwa***@msn.com> wrote in message news:%23yZt9I4pGHA.4912@TK2MSFTNGP05.phx.gbl... > How do I delete rows that are older than 180 days if the table does not > contain a column of timestamp? The following statement works fine if > parameter startdate is known: > > DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180 > > Thanks in advance. > > -tc > Thank you very much, guys. I think I will add a timestamp column to my table
next time. -tc Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eo6cup4pGHA.148@TK2MSFTNGP04.phx.gbl... > SQL Server does not track this information, so you're going to have to add > a column like: > > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT > CURRENT_TIMESTAMP; > > Of course, you won't be able to take advantage of the values for this > specifc task until 6 more months have passed. > > A > > > "tcw" <tcwa***@msn.com> wrote in message > news:%23yZt9I4pGHA.4912@TK2MSFTNGP05.phx.gbl... >> How do I delete rows that are older than 180 days if the table does not >> contain a column of timestamp? The following statement works fine if >> parameter startdate is known: >> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180 >> >> Thanks in advance. >> >> -tc >> > > "tcw" <tcwa***@msn.com> wrote in message Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.news:egpsl9GqGHA.4932@TK2MSFTNGP05.phx.gbl... > Thank you very much, guys. I think I will add a timestamp column to my table > next time. Timestamp is a separate datatype which actually doesn't map to date or time. Show quote > > -tc > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message > news:eo6cup4pGHA.148@TK2MSFTNGP04.phx.gbl... > > SQL Server does not track this information, so you're going to have to add > > a column like: > > > > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT > > CURRENT_TIMESTAMP; > > > > Of course, you won't be able to take advantage of the values for this > > specifc task until 6 more months have passed. > > > > A > > > > > > "tcw" <tcwa***@msn.com> wrote in message > > news:%23yZt9I4pGHA.4912@TK2MSFTNGP05.phx.gbl... > >> How do I delete rows that are older than 180 days if the table does not > >> contain a column of timestamp? The following statement works fine if > >> parameter startdate is known: > >> > >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180 > >> > >> Thanks in advance. > >> > >> -tc > >> > > > > > >  |
|||||||||||||||||||||||
Other interesting topics