Updating Modified/updated Date Column?
If your tables contain created and modified/updated dates what is the best practice for these?
1. Should you use UTC dates?
2. Do you use a default for the creation date (I assume yes)?
3. Should you create a trigger to handle the last update date? Or do you update the column directly in your stored procedures that modify data?
Also, as an aside if you store the user who created/updated the record do you store a foreign key reference to the user table or do you store the username as a varchar? Of course I know you'd normally store the fk, but I wasn't sure if the "logging" nature of the column suggests storing a string value.
View Complete Forum Thread with Replies
Related Forum Messages:
Finding Last Updated/modified Date
Hi, I am using SQL Server 2000 and have the following questions: 1. How do I know the last updated (data) date using system objects or any other method? 2. How do I know the last modified date of a table using system objects or any other method? 3. How do I know when a table is last accessed Any help is appreciated Thanks for your time and help in advance Kumar
View Replies !
Can &"Date Modified&" Col Be Automatically Updated W/o Trigger For Each Table?
Hello, I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated? Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables. Thanks!
View Replies !
Table , Column Created, Modified Date In Sql Server 2000 Or 2005
Hi Any one please tell me is there any possible way to identify the table modified date. I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates. Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000. I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date can any one help
View Replies !
Save Updated Date When Row Is Updated
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View Replies !
Modified Date
I am taking a "complete backup" of my production db every day using Backupagent of Arcserver 2000. But the Modified Date of .mdf and .ldf files show an older date. Is it normal? Thanks Wilson
View Replies !
Modified Date
Is there a way to see the last modified date on the Table structure or a stored Procedure in SQL Server 2000? Thx
View Replies !
Get Modified Date Of File
Hi, the ssis package loops through a folder using a foreach loop container and imports the data inside each .csv file into the database. Now I would like to add the functionality to first check the modified date of the .csv file. If it is NOT today's date then the package should fail or go to error. Any thoughts how to do this please? Thanks
View Replies !
File And Date Modified
Hello all, I was just thinking about a situation which I have been tasked with in that I have a package that is scheduled to run once a day which will import data from a flat file. The data does not have any distinguishing time that can be used to see whether or not it is beyond a last imported time. I was wondering, is there a way to access the date modified of the file? If so I could simply append this time to a table which keeps track of all of the files and the modified dates which I have ever imported for this task.
View Replies !
Get File Modified Date
Hi, the ssis package loops through a folder using a foreach loop container and imports the data inside each .csv file into the database. Now I would like to add the functionality to first check the modified date of the .csv file. If it is NOT today's date then the package should fail or go to error. Any thoughts how to do this please? Thanks
View Replies !
Win Date Modified Field On MDF Files
I'm an Oracle DBA and just getting used to MS Sqlserver. I noticed that the windows explorer "date modified" field for my database files ( .MDF files ) doesn't change much even though there is activity going on. Sometimes it doesn't change for a week. Is this the expected behavior? Could it be that no data is changing in my database? ( I find that hard to believe) Thanks for any insights.
View Replies !
File - Date Modified Attribute
I am looking to create an SSIS package to import text files into an SQL table. I'd like to import the 'date modified' attribute from the text file into one of the columns as well as the data within the text file. I'm thinking an ActiveX task and variable currently, just wondered if anyone had any other thoughts on how this could be achieved. Thanks
View Replies !
Sql Database File Last Modified Date And Attribute Question
I have a database that is actively being used and updated. When I look at the last modified date of the mdf file, it shows as a couple of weeks ago date and the transaction log file .ldf file shows a couple of days ago. wonder if the database is constantly updated and changed, should it show the currently date instead. Also, the attribte of mdf file show A as archive. what does it mean? Thanks
View Replies !
Last Date Stored Proc Updated???
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!
View Replies !
Sql Server 2005 And Stored Procedures - Updated Date?
Hi, I was wondering, is it really true that in Sql Server 2005, there is no updated date -property for stored procedures? There is a created date -property, but it's not useful, because stored procedure's get updated often. This is a huge disadvantage for me, and I was really waiting for ms to fix this problem after sql server 2000, but no. Does anyone figured out any solution to this problem? Br, Riika
View Replies !
Any Column Updated / Inserted Trigger
Hi,I'm a SQL Server newbie, so I'd appreciate if someone would tell me ifthis is possible. I'm running SQL Server 2000 on Win2k ServerI have one table with a large number of columns. I have two pieces oflogic that I'd like to execute depending upon whether an insert or anupdate statement was executed on that table. I'd prefer thisexecution to occur from within a single trigger. If a row isinserted, then I would like to execute logic A. If ANY column in thetable is updated, then I'd like logic B to be executed.Is it possible to just determine if only "insert" or an "update"ocurred from within the a single Trigger, without specifying eachindividual column name? (I.E. not saying IF udpate(col1) orupdate(col2) or ect...) Is it possible to just perform a check on theprocess that occurred, irregardless of column? Like If INSERTED =TRUE then execute insert logic. If UPDATED = TRUE, then run theupdated logic. I would like for all of this code to be stored withinthe same trigger.If anyone can provide some sample code on how to do this, if at allpossible, I would be much appreciative.Thanks,-RigsPS I know I could do this with 2 seperate triggers, but I'm trying toavoid that.
View Replies !
Updating The Column Defined As 'Text' Column
Hi, I have a table with col_noteText defined as 'Text' datatype column. I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText. Does anyone know how to do this for 'Text' datatype column. Thanks in advance. jfk
View Replies !
Updating Database Date Field Results In Date Value Of &"01/01/1900&"
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
View Replies !
Linked Reports Not Being Updated When Master Report Is Updated
Since updating to SQL Server 2005 SP2 I've noticed two things about Linked Reports. 1. I do a lot of 'Snapshot' reports. With SP-1 if I updated a master report and made any changes to the Parameter List - it undid all my custom parameter changes on linked versions (restored to the Master Reports Defaults). While this is no longer happening with SP2 - it is still 'unhiding' the parameters. 2. With SP-1 if I added/deleted columns or made other changes to the report structure - the linked reports would pick up on the changes with their next refresh. With SP-2 I'm finding that I have to 'Re-link' the linked report back to the master report before the changes are refreshed. This is very time consuming especially with each report having 8 or more Snapshot reports pre-set up. Am I missing something - or is this a 'bug'... Any help would be appreciated...
View Replies !
Updating A Column With A Column From Another Table
Hi, I am kinda new to this so here it goes: table name: USERS field names : LName, FName, EmpID other table name:PERSONNEL field names : (same as USERS) The EmpID column in my USERS table is blank (I have 30 records in the table.) I would like to update the USERS.EmpID table with the PERSONNEL.EmpID data how would I do this or what would the code be? sanctos
View Replies !
Updating Max(column) And Between_dates Column
Hi, I have dataset which has max(column) and between_sale_dates columns So I would like to update those columns. CustomerNo and Sales_date are important . This group by for these 2 colums. I can write a sproc but How can I do with SSIS? thanks
View Replies !
Updating Next Run Date Value
If after scheduling a job the next run date value is (Date and Time arenot available), how can that be updated? It is my understanding thatthat value is used by SQL to run the job.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Updating Date
Hi, I have around 8 date columns in a table I want to update the columns for eg initialization date should be greater than incident date .but the thing is it should be just one day or upto 4 days greater . Right now all the tables have random dates ..I tried dateadd i also tried datediff nonis helping please can u help sambhav jain
View Replies !
Updating Date Field
All other fields are updating ok and I'm not getting an error.I am trying to update a date and time (smalldatetime) using a stored procedure.First, the info to be updated comes from a datagrid. Dim sDate As DateTime sDate = CType(e.Item.FindControl("tDate"), TextBox).TextThen, passed to the SQLDal class and then to the stored procedure..... Public Function updateData(ByVal sDate As DateTime, ByVal sp As String) 'Some items snipped for easier read Dim command As SqlCommand = New SqlCommand(sp, conn) 'Where sp is the stored procdure name command.Parameters.Add("@date", sDate)'And so on.....And then the stored procedure....@num VARCHAR(256),@date SMALLDATETIME,@contact VARCHAR(256),@notes VARCHAR(8000),@media VARCHAR(256) ASBEGIN DECLARE @errCode INT BEGIN TRAN -- UPDATE THE RECORDS UPDATE dbo.tblData SET fldDate = @date, fldContact = @contact, fldNotes = @notes, fldMedia = @media WHERE fldNum = @num <sniped>Like I said, all other fields are updated with no problems, but not the date.The date format being passed into the sp is {0:MMM dd, yyyy hh:mm tt} or Aug 05, 2005 04:39 PM Is it the format of the date? Or something else I'm not seeing...Thanks all,Zath
View Replies !
Updating Date Format
Hello, My date format is set to smalldatetime like: yyyy/mm/dd I need to convert all date formats on this collumn to dd/mm/yyyy How could I do this?
View Replies !
Splitting SQL Server Date/Time Column Into Access Date Column And Access Time Column
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time. What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column? Thanks, Steve
View Replies !
Gridview Date Field Not Updating
Hi - Once again I've been looking at this forever and not able to see the problem. Have a grid table everything updates except the training date field. That get's wiped out each time - no matter if something is in it or not. Everything else updates correctly. Here's the code: <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><Columns><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True"><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:BoundField><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:TemplateField> <asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>" ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"UpdateCommand="UPDATE [trainingLog] SET [trainingDate] = @trainingDate WHERE [eventID] = ?" > <UpdateParameters><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
View Replies !
Updating A Column By The Value Of '1'
Hi,Its probably simple but.. How do I update a column by just '1'..for example - heres my code: protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(); con.ConnectionString = "HiddenConnection"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; Label productIDLabel = (Label)DataList1.FindControl("productIDLabel"); command.CommandText = "UPDATE Items SET numberclickedin = numberclickedin + 1 WHERE productID=@productID"; command.Parameters.Add("@productID", productIDLabel.Text); command.ExecuteNonQuery(); con.Close(); command.Dispose(); } As you can see in the bold text, I want to add 1 to the numberclickedin column.. and in my primative way Ive just typed +1What should I use instead?Thanks in advance,Jon
View Replies !
Updating A Column In SQL
I am trying to update a users status from Pending to either Approved or Rejected. I created the following handers to update me db by I keep getting a syntax error. What am I doing wrong? public partial class admin_beta : System.Web.UI.Page{ protected void ApproveButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Approved"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); } protected void DenyButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Rejected"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); }}
View Replies !
HELP Updating Column
How can I create an update statement that will allow me to fill in a column if the previous column already has data in it? I am trying to do and UPDATE/SET command that allows me to extract information and populate columns within a table. However, the UPDATE/SET has to make sure that it is not overwriting information that is already in the column and if there is, to populate the column next to it, and so on until all the columns are populated. Here is my table: Code: create table #add_diags( add_diag_1 varchar(10) null, add_diag_2 varchar(10) null, add_diag_3 varchar(10) null, add_diag_4 varchar(10) null, add_diag_5 varchar(10) null, add_diag_6 varchar(10) null, add_diag_7 varchar(10) null, add_diag_8 varchar(10) null ) In my UPDATE/SET I am pulling data from another table to populate into the 'add_diag' columns however, I'm not sure how to write in SQL the ability to monitor all the columns, and if for example the first three are full to then populate into the fourth and so on. Here is my UPDATE/SET statement (while involved, I think I need something in my WHERE clause in order for this to be resolved): Code: update ad set add_diag_1=dsm_code from #add_diags ad join Doc_Entity de on ad.patient_id=de.patient_id and ad.episode_id=de.episode_id and doc_code = 'DCDIAG' join Patient_Assignment pa on de.patient_assignment_id = pa.patient_assignment_id and convert(char(8),de.effective_date,112) = convert(char(8),pa.date_discharged,112) left outer join Doc_Diag_Axis_I_III dx1 on de.doc_session_no=dx1.doc_session_no and de.current_version_no=dx1.version_no where dx1.sequence_no=2 and de.is_locked = 'Y' and dx1.rule_out = 'N' and is_billable = 'Y' and dx1.axis_type IN ('1','2') and de.status in ('CO' , 'SA') Any ideas? Thanks!
View Replies !
|
TRACKER
