473,549 Members | 2,862 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Jul 20 '05 #1
2 10945
Hi

Timestamp is not a character field.. as stated in books online:

A nonnullable timestamp column is semantically equivalent to a binary(8)
column. A nullable timestamp column is semantically equivalent to a
varbinary(8) column.

When you select the column in QA, it will be displayed as a hexadecimal
number.

If you include DDL ( Create table statements etc... ) and example data (as
Insert statements) along with your query and the output, it may be clearer
what is occuring.

John
"Astra" <in**@NoEmail.c om> wrote in message news:40******** **@127.0.0.1...
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

Jul 20 '05 #2
Astra (in**@NoEmail.c om) writes:
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.


Since timestamp is 8 bytes and int only 4, a cast to int could lead to
funny things.

On the other hand, ORDER BY TIMESTAMP DESC will give you the rows in
some order that pertains to updates - and inserts. But it can be a bit
rough. Say that you at some point reloaded the table because of some
maintenance thing, that gave you new timestamp values.

So you are probably better off adding a "moddate" column, which you
update in case of "true" updates, either through stored procedures or a
trigger.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1572
by: zaceti | last post by:
I'm new to MySQL and I am having a problem selecting the highest valued date/time for a particular day. Here is the table structure: +--------------+-----------------+-----+----+--------+--------------+ | Field | Type | Null| Key| Default|Extra | +--------------+-----------------+-----+----+--------+--------------+...
4
3253
by: Ike | last post by:
Is there a way in mysql to discern when the last date/time a field, row, or table was been updated or inserted? Thanks, ike
6
14827
by: Ray | last post by:
Is there a simple way of selecting the last backup timestamp of a database from a table? Something like select last_backup_timestamp from tablename where database_name='WHATEVER' Or am I forced to check the backup history with list history backup all for WHATEVER
1
3191
by: gnuoytr | last post by:
i can't find either confirmation or contradiction in the docs available. a colleague asserts that a column defined TIMESTAMP is always and automatically updated when any column in the row is changed; no triggers or application code needed. is he smoking something he shouldn't be?
4
2952
by: vulcaned | last post by:
Hi All, Hopefully I explain this well........ In Access97 I have a form which has a tab control on it, each tab has a sub-form which is bound to its appropriate table(I'll call them 'Detail' tables). I have a 'Header' table and fields from it are displayed on the form above the tab control. Header table name is tblClientInfo Detail tables...
3
1853
by: Joe Kimbler | last post by:
I'm writing a "Hot Directory" software package that watches for an MP3 file to be uploaded to an FTP directory. When the file has completed uploading, it will be converted into a WAV file and imported into an automation system. The problem that I am having is telling if IIS (FTP) is done uploading the file and it is no longer in use. ...
1
2458
by: Antony Paul | last post by:
Hi all, Is it possible to know when a row was last updated. Using 7.3.3. rgds Antony Paul ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing...
6
1476
by: MLH | last post by:
I have a table - tblCorrespondence. It houses records of correspondence initiated here in our office. Records are appended whenever it is recognized that some type of outbound letter needs to be created and sent. The table contains and fields. The 2 fields are updated when the letter is printed, permanently marking it as having been...
0
1164
by: jaimebienlesfruits | last post by:
Is there a script I can include on the bottom of each page to indicate when the page was last updated? The PHP timstamp thing doesn't seem to work as it reflects the date of visit on the user's part. Thanks:D
0
7484
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7755
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7997
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7515
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7842
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5400
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5124
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1974
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1089
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.