keeping track of modification date of a row

timestamp type seems to be the design to keep tracking of the modification
date (at least it's convertible to datetime) of a row; but is there a better
way to keep track it with a datetime type? I hope to avoid doing the
conversion everytime I need to look at the data (many rows at a time.)
Creating trigger is obviously possible but I hope for something simpler.
thanks!
TIMESTAMP has absolutely nothing to do with date or time! Can you show how
you are converting it to a datetime value, and demonstrate a case where
TIMESTAMP is accurately tracking the last date/time a row was updated?
Use a LastUpdatedDate column and update it with a trigger or, if you control
access to the table via stored procedures, you can use the stored procedure
to include an update to that column whenever any other value in the row is
touched.
"Zester" <zeze@.nottospam.com> wrote in message
news:eteRVyfWIHA.5716@.TK2MSFTNGP05.phx.gbl...
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a
> better way to keep track it with a datetime type? I hope to avoid doing
> the conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
|||Hi Zester,
If you are working either with SQL Server 2000 or 2005, you have no other
chance than triggers.
But if you can wait until SQL Server 2008 arrives, things will be different.
You will have tracking features included on the server, along with two new
datatypes: DATE and TIME. (Separated at last!!!)
Hope this would be helpful. Please, rate this post. Thanks!
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Zester" wrote:

> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a better
> way to keep track it with a datetime type? I hope to avoid doing the
> conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
>

keeping track of modification date of a row

timestamp type seems to be the design to keep tracking of the modification
date (at least it's convertible to datetime) of a row; but is there a better
way to keep track it with a datetime type? I hope to avoid doing the
conversion everytime I need to look at the data (many rows at a time.)
Creating trigger is obviously possible but I hope for something simpler.
thanks!TIMESTAMP has absolutely nothing to do with date or time! Can you show how
you are converting it to a datetime value, and demonstrate a case where
TIMESTAMP is accurately tracking the last date/time a row was updated?
Use a LastUpdatedDate column and update it with a trigger or, if you control
access to the table via stored procedures, you can use the stored procedure
to include an update to that column whenever any other value in the row is
touched.
"Zester" <zeze@.nottospam.com> wrote in message
news:eteRVyfWIHA.5716@.TK2MSFTNGP05.phx.gbl...
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a
> better way to keep track it with a datetime type? I hope to avoid doing
> the conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>|||Hi Zester,
If you are working either with SQL Server 2000 or 2005, you have no other
chance than triggers.
But if you can wait until SQL Server 2008 arrives, things will be different.
You will have tracking features included on the server, along with two new
datatypes: DATE and TIME. (Separated at last!!!)
Hope this would be helpful. Please, rate this post. Thanks!
--
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Zester" wrote:
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a better
> way to keep track it with a datetime type? I hope to avoid doing the
> conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
>

Keeping dirty data...

The organization I'm in has the business need of collecting data from
outside organizations and tracking what data is bad and what data is
good. When I say bad data I mean everything from things outside of
range to absolute crap - characters in integer columns, integers in
character columns, special characters, etc. The data comes in in the
form of flat file so it's a free for all until it hits ssis & the db
engine.
Eventually of course they work to get the data corrected at the source
& resubmitted but in the meantime, they have the legitimate need of
not only pushing the data into the database (dirty or not), but
keeping all the bad stuff, running reports on the bad stuff, etc. I
can't in good conscience make everything a varchar to catch everything
- that would go against the database gods. IMO - I still must make an
integer be an integer , characters are characters, etc. But what do I
do with the junk? Any thoughts? Right now I'm throwing everything
over to some side catch-all tables as varchars, and pushing clean data
into real tables, but that feels wrong too. Suggestions?You're doing exactly what a lot of people do in BI applications - just using
an intermediate staging area to temporarily store data until you run the
next process to clean it. Some people prefer to push the data directly into
the production system without an intermediate "staging" area, and just let
the SSIS error flows catch the bad rows while importing from flat files.
You might find this method more efficient, since you don't need two separate
processes.
"CB" <unc27932@.yahoo.com> wrote in message
news:415c48ac-cfad-4d70-9619-4dec8cd062ac@.l1g2000hsa.googlegroups.com...
> The organization I'm in has the business need of collecting data from
> outside organizations and tracking what data is bad and what data is
> good. When I say bad data I mean everything from things outside of
> range to absolute crap - characters in integer columns, integers in
> character columns, special characters, etc. The data comes in in the
> form of flat file so it's a free for all until it hits ssis & the db
> engine.
> Eventually of course they work to get the data corrected at the source
> & resubmitted but in the meantime, they have the legitimate need of
> not only pushing the data into the database (dirty or not), but
> keeping all the bad stuff, running reports on the bad stuff, etc. I
> can't in good conscience make everything a varchar to catch everything
> - that would go against the database gods. IMO - I still must make an
> integer be an integer , characters are characters, etc. But what do I
> do with the junk? Any thoughts? Right now I'm throwing everything
> over to some side catch-all tables as varchars, and pushing clean data
> into real tables, but that feels wrong too. Suggestions?