I know an SQL Server timestamp seems to be as useful as rocking horse for
show jumping, but I'm hoping you know a 'fudge' to get me round a problem or
at least confirm that it isn't possible.
I have 2 tables, one called ACCOUNTS and one called STOCK.
These tables have the usual ints, varchars, etc and have a timestamp field
as well.
My end user wants to see a simple list of the details in these tables
(individually - no joins present here), but sorted from most recently
updated to never touched.
As the timestamp seems to update each time a transaction hits it I though
this would be perfect, but I've had the following mixed results:
1) I did a simple ORDER BY TIMESTAMP DESC, but because the order is
alphanumeric I don't get a true 'recent to old' list.
2) So I did ORDER BY CAST(TIMESTAMP AS INT) DESC. There is a numeric sort
now, but the timestamp values seem to be hit and miss so that an account
that I know should be near the top is around about the middle.
I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?
I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?
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
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
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.
For starters, please feel free to move this if it is in the wrong forum.
The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?
Hi Im doing a simple update in my SP: update users set Name = @Name where id=@userID and password=@password i want to know if a row gets updated. for example if the userID and password dont match then the row will not get updated.so i want some way to tell if a row has been updated. how do i do this?thanks
I have a timestamp in a date field of a table I have but when run it does not enter the date automatically and all I get is system.byte[] display where the dtae and time should be, how can i get it so this field places the date in automatically.???
I am retrieving data from a database, and have been noticing some really strange timestamps. Has anyone ever seen a timestamp that looks like this: 16777215Karls
I just started using SQL and i don't really know too much syntax.
I have an ASP page that is inserting data into a table called YellowAlerts. The first field in this table is AlertDate. I thought I read somewhere that there is a timestamp feature. If anyone could help me out that would be great.
I believe that all created tables have got by default a timestamp field. I tried to find out how to read this field with a Transact-SQL statement. And so I don't know the syntax, is anyone can help me ?
If i have a table with a 3 fields and one of them has a timestamp as a field type and I want to do an insert or update what is the value I am inserting for the column timestamp.
hello I put a time stamp in one of my column and it says binary data in the field. How do i make that binary data turn into a real date and time. If this is possible how do I separate the date from the time in the results or would I have to do this myself. Also, I need to sort the results by date can this be done?
Sorry for the stupid questions my sql book (sql demystified) does not elaborate. Thanks
hello, m new to sql server.can any one tell me is it possible to insert only time value in timestamp datatype. i have table which have datatype datetime.and i have to insert only time.i can't change the datatype datetime to any other one.p plz any one can suggest me the solution..
Hello I'm not a SQL Expert but i'm using it for a few months. Now i need to use a variable that everytime that is called to a select into it gives the timestamp (or Stamp) to put on a specific field. The problem is that i need this variable not in bd conotation (like 2007-02-12 12:00:44:33) but like this 2007021212004433. Can someone help me? Thanks in advance.
Is there any reason to have a TimeStamp column onto a table that has aPrimaryKey when using SQL Server and an Access front end?I read that doing so will always eliminate that write conflict error.But I notice that when pulling a recordset of 10K records, it takes 3Xless time if I delete the timestamp column on the table before runningthe SP.lq
Is there any chance to get the content of a timestamp field?I am trying to get the content via Visual Objects and I am receiving onlynothing.Not NIL but "".Is it possible to get any useful information out of this field in anylanguage?(btw I know that the MS-Timestamp is not a timeformat)THXJens
Hi everybody, I am a total noob conserning ASP, but I am willing to learn We have a sql2005 SRV(hosted by our ISP, so limited access) and a ASP based forum (WEB WIZ) When I try to login I get this error: Support Error Code:- err_SQLServer_loginUser()_update_USR_CodeFile Name:- functions_login.aspError details:-Microsoft OLE DB Provider for ODBC DriversQuery cannot be updated because the FROM clause is not a single simple table name.Can somebody tell me whats wrong? Thanx in advance. Gerry de Bruijn!
Hi - apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit. I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried: 1 UPDATE Timeslot 2 SET StartTime = @StartTime, EndTime = @EndTime 3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND 4 (EndTime < @EndTime); 5 SELECT SCOPE_IDENTITY()
My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row? Thanks!!! Oliver
How can I determine who and when a table in a db was updated, I dont need this per record, I only need to know the last time an update was made to a table. Thanks.
If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated?
CREATE PROCEDURE usp_Structural_ScheduleComponent @cProject char(7), @cComponentID char(10), @iPour int, @iQuantity int, @iAvailable int OUTPUT, @dtCast datetime OUTPUT AS
SET @dtCast = convert(char(10), getdate(), 120)
DECLARE @cSql varchar(500) SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' + ' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + ' FROM tbStructuralComponentSchedule ' + ' WHERE fkProjectNumber = ' + '''' + @cProject + '''' + ' AND fkComponentID = ' + '''' + @cComponentID + '''' + ' AND IssueDate IS NOT NULL' + ' AND ScheduledDate IS NULL' + ' ORDER BY EntryID DESC)'
EXEC(@cSql) IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0) RAISERROR('Failed to add components to pour!',16,1)
SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END) FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID
GO
-- Is there a way to return the recordset that were modified in the update?
