Finding Last Updated With A Timestamp
Hi All
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.
Do you know how I can achieve the results I want?
Is the timestamp a waste of time?
Thanks
Robbie
View Complete Forum Thread with Replies
Related Forum Messages:
Finding Updated Tables
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?
View Replies !
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 !
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 !
Error While Converting Oracle Timestamp To Sql Server Timestamp (datetime) - &&"Invalid Date Format&&"
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.
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 !
Get Row Timestamp With No Timestamp Column
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?
View Replies !
Timestamp
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
View Replies !
Db Timestamp
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.???
View Replies !
Timestamp
how to insert a value into table where the fieldname uses a datatype timestamp.(asp.net2.0)
View Replies !
SQL TimeStamp Help
Hello, 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. Thanks.
View Replies !
TImeStamp Or Not
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
View Replies !
Timestamp
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
View Replies !
TimeStamp
Is there any reason at all to use a timestamp column in a table havinga primarykey column???lq
View Replies !
Timestamp
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. Help appreciated
View Replies !
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
View Replies !
Timestamp
I have inherited a table with a timestamp column. I would like to call a stored procedure (within another sp) based on the timestamp reflecting the current day. Does anyone have any suggestions on reading the timestamp data and doing a comparison to todays date ? Do I have to convert the timestamp data first ? Thanks in advance Clea Boe
View Replies !
Bcp And Timestamp?
hello: I am new to SQl Server 6.5. We have a new application and just got the application code. Howver, I notice that each table has a edit_timestamp with the datatype of timestamp. During our testing, I will have to use bp to take data in and out of the tables, My questions are: 1) Will the timestamp datatype be difficult to use with bcp? 2) Would I be better off asking the vendor to change the data type to datatime rather than timestamp? Thanks. DAvid Spaisman
View Replies !
Timestamp
Hi All, 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 ? Thanks, Herve
View Replies !
TimeStamp
How do I "disable" the timestamp so that when I copy my data into a new database it will use the timestamp from the last time the table was effected? Thanks, Tracy
View Replies !
Timestamp
How to find the maximum or the current inserted or updated data from timestamp column in the table...?
View Replies !
Timestamp
Hello all, I've just taken over a database on sql 7 that contains the timestamp column. This a binary datatype column. From what I understand, the timestamp column is a unique sequence value. How can I or if can I determine the date of the change from the timestamp value? Thanks Pauli
View Replies !
TIMESTAMP --- HELP
How can I add a timestamp column to a table that will let me know when a record was inserted? (perhaps by setting a default to that column?) For some reason, @@currenttimestamp gives me some binary garbage. Please help with any suggestions.
View Replies !
Timestamp
Hi there, When inserting into a table with a timestamp column without explicitly setting the column_list you get the following. ie. insert into Table_A select * from Table_B Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column. I was wondering if there's a way to overcome this without specifying the column list? Thanks.
View Replies !
TimeStamp
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.
View Replies !
About The Timestamp
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.. Regards, Sonal.
View Replies !
How To Tell If A Row Is Updated
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
View Replies !
Updated
Thanks i just re tested again i think i had the database selected on the wrong one and was getting incorrect results. Seems to be working just as i thought. Snapshot got record 1 here then if udpate to 2 then this does not update the 1 to 2 Cheers
View Replies !
SQLDataSource And Timestamp
Hi folks, I am binding a gridview to a SQLDataSource that contains a timestamp column. My SQLDatasource contains an update method (via a stored procedure) that takes all of the data fields from the grid as input parameters. I am getting an error stating 'sql_variant is incompatible with timestamp' . The parameter definition for the update method specifies the timestamp column as type 'Object'. <UpdateParameters> <asp:Parameter Name="timestamp" Type="Object" /> ... </UpdateParameters> When the data is initially retrieved, the timestamp column is converted to a System.Byte[] however I cannot specify System.Byte[] in the parameter definition (different error) I have read other post with this issue but none seemed to have been solved (while still using SQLDataSource and binding).I have tried other datatypes and they do not work as well. Any help would be greatly appreciatedThanksTom
View Replies !
Timestamp Database
HI every one I have a timestamp column in my database. I want to use this column to get current changes but i am not able to get desired results when i use to compare that field in C#. Thanks in advance Take Care ALLAH HAFIZ
View Replies !
Timestamp When Updating
Hi world,I would like to know how to maintain a table with a "timestamp" column. I mean a column that automatically set the current datetime when the row is updated.I don't want to go trought my application and set that in the update or insert command. I would like to know if there is a possibility of putting a trigger or something.thx
View Replies !
Using Timestamp With ASP.net / SQL Server
I have a database that I don't want to lock. I decided that before any updates can occur I would check a timestamp value and ensure that nobody else updated before I did (avoiding the 'last update wins' scenario). I have a problem, I can read the Timestamp from the db when I read the record. I currently use the data to pre-fill a form (gee go figure ;) ) and the user changes some values and updates. I don't know what to DO with the timestamp value while I am holding it. I have tried putting it into a hidden field on the form but the value does not seem to translate back and forth. Do I have to store it as in memory as part of the session or can I somehow convert to and from text?? I have about 0 experience working with byte arrays so the best answer is one with an example. Thanks.
View Replies !
Timestamp Problem!
Hello Group,I am having a really weird problem... Can anyone tell the differencebetween Query 1 and Query 2 below? Why Query 2 excludes '2/28/2005'?Many thanks!create table a(ApplicationID int,timestamp datetime)insert into a values(1111,'2/26/05 3:24')insert into a values(2222,'2/25/05 19:03')insert into a values(1111,'2/4/05 14:54')insert into a values(2222,'2/28/05 23:10')--Query 1--select year(timestamp) app_year,month(timestamp) app_month,day(timestamp) app_day,count(distinct applicationid) numappsfrom awhere year(timestamp)=2005 and month(timestamp)=2group by year(timestamp),month(timestamp),day(timestamp)order by year(timestamp),month(timestamp),day(timestamp)--Query 2--select year(timestamp) app_year,month(timestamp) app_month,day(timestamp) app_day,count(distinct applicationid) numappsfrom awhere timestamp between '2/1/2005' and '2/28/2005'group by year(timestamp),month(timestamp),day(timestamp)order by year(timestamp),month(timestamp),day(timestamp)
View Replies !
Timestamp Via Java
hi all and sorry for my englishI write into a SqlServer2000 database via jdbc using a PreparedStatement(pstmt) and one of fields is a TimeStamp, my code is:qwdum=System.currentTimeMillis();pstmt.setTimestamp(8 , new Timestamp(qwdum));sometimes the field on table is correct (the milliseconds are the same oflast 3 digits of variable qwdum), other times it has a difference of 1 o 2millisecondsthe same code with jdbc Oracle or iSeries works fineany idea?thanksRoberto Nenni
View Replies !
Timestamp Precision
I am using ASP and SQL 2005 Express.I am inserting a timestamp from an ASP page using <%=now%into asmalldatetime field. All of my timestamps are appearing without anyseconds (e.g., 1/21/2008 4:02:00 PM or 1/18/2008 11:32:00 AM).When I view the source for my page is shows the date/time as 1/21/20084:27:31 PM, but for some reason the seconds will be converted to1/21/2008 4:27:00 PMHow do i get more a more precise timestamp?Please help.
View Replies !
Timestamp Conversion
I am working with an application that is returning a second basedtimestamp. It returns values based on the 86440 second day in GMT.Is there an ability within SQL Server to convert the value into anhour time value?I need to take this value and convert it to a hour value for the timezone the client is in.Thanks,Dave
View Replies !
Timestamp Curiousity
Hi all, I'm developing a Java-application in which I read data and update a series of documents from several tables in SQLServer 2000. Now - finally seeing the benefit of timestamp columns - I want to only update the the documents where the data in the SQL tables has changed (performance and so forth). I started out by reading the timestamp column as a String object thinking that if I saved this value in my documents I would have something to compare the value against. But it seems to me that this value changes everytime I query the table (its string-representation anyway). So now I'm curious: Event though I do not update the tables (my query is defined as READ_ONLY) does that change the timestamp value. Or should I convert the value to something else that a String. Or is it just plain imposible to do any calculus on a timestamp-column ?? Any help will be greatly appreciated.... Regards - and thanks for your time Kim Hansen
View Replies !
TimeStamp Issues
i'm using a starttime, stoptime, duration combination of columns. i'm using datetime for the first two to get a time stamp for the start and stop. the issue is that i want to display "00:00" for start and stop time if the session has not been started yet. is this possible??? what is the best course of action to tackle this problem?? thanks, e3witt
View Replies !
Replication Timestamp
Hi, from the url--- http://support.microsoft.com/default.aspx?scid=kb;en-us;820675 I did the steps to implement the replication. However, i did not include a timestamp column in the tables. Is it important as to what would happen if i did not include this? I have the rowguidcol though. I thought this could would help to identify the various row. Help pls? :confused:
View Replies !
Row Insert Timestamp?
Just a quick question: Does SQL server 2000 store any identity information for tables in terms of the time a row gets inserted or are you forced to track this yourself? Thanks!
View Replies !
Date Timestamp
i am creating a DTS package that will query a table and move data to another table daily on same DB/SERVER. i want to be able to timestamp previous date into date column in destination table. There's no date on source but i need timestamp on each import and date for previous day b/c importing data are for previous day.
View Replies !
How To Retrieve Timestamp
Can any one please tell me how to retrieve values from timestamp column. I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified. Is there anything I need to set it up. thanks
View Replies !
Timestamp Columns
Hi, Is it possible to add timestamp column to an existing table? Any considerations, precautions etc. dealing with timestamp columns? Thanks, Rohan.
View Replies !
Timestamp Problem
I have a table in SQL server 6.5 which has two columns. One is a timestamp column and another is an int column. Now I need to change the int column to numeric (16). For this I cannot use the alter table statement as it is not there in sql server 6.5. I tried creating another table with two columns- one as numeric(16) and the other as timestamp. I transferred the data from he old table to the new table and dropped the old table and finally renamed the new table as the name of the old table. But the problem is that the timestamp column now contains data that is different from the data that was there in the original column. I cannot update the timestamp column as that is done automatically neither can I insert the values. How do I make sure that the data in the timestamp column remains the same. Any help would be greatly appreciated. Vikram
View Replies !
Timestamp Data
Hi! I have timestamp column and as you know the data there is not in readable format. what should I do to get normal date to find out when row was updated by user. Thank you in advance, Nadia.
View Replies !
DTS Rejects Timestamp
I would like to transfer all the tables from one database to another. I am using DTS. All goes well as long as a table does not contain a timestamp field. DTS says that the table contains fields that are read only. Also identify fields lose their 'Identity" flag. Can't I just make a mirror image 'copy'? I just want all the data in the tables transferred (copied) to another database. Thanks, Judith
View Replies !
Timestamp Problem
hi all, I have a desktop edition of SQL server installed on nt.4.00.sp 5 from what i know..a timestamp field should insert current date & time...right? So.. if i run these statements: /****** Object: Table [dbo].[T1] Script Date: 12/7/99 10:45:49 AM ******/ if exists (select * from sysobjects where id = object_id(N'[dbo].[T1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[T1] GO /****** Object: Table [dbo].[T1] Script Date: 12/7/99 10:45:51 AM ******/ CREATE TABLE [dbo].[T1] ( [column_1] [int] IDENTITY (1, 1) NOT NULL , [column_2] [varchar] (30) NULL , [column_3] [int] NULL , [column_4] [timestamp] NULL ) ON [PRIMARY] GO INSERT INTO T1 (column_2,column_3)VALUES ('new', 123) INSERT INTO T1 (column_2,column_3)VALUES ('new1', 1234) INSERT INTO T1 (column_2,column_3)VALUES ('new2', 12345) go SELECT cast(column_4 as smalldatetime) FROM T1 go I get this result: (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) --------------------------- 1900-01-01 06:51:00 1900-01-01 06:52:00 1900-01-01 06:53:00 (3 row(s) affected) my clock is running up to date... I have no idea why i'm getting this..is this a bug...??? Please help...
View Replies !
Timestamp Conversion
I'm having trouble converting a timestamp value passed into a stored procedure as a char to binary(8). For example, the value passed in is '00000000000002CA'. I want to convert to a binary(8) value so I can use this in the where clause of a query. I can't seem to get the conversion to work correctly. Thanks in advance for your help.
View Replies !
|
TRACKER
