|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server 2005 timestampHi!
Is it possible to use comparisons sql server 2005 timestamps, like operators greater than, less than on two sql server timestamps? We want to compare two fields in a record to see what is the latest, and so on. If you know how, please give me an example on how to do it? Thank you. You need to provide a bit more information. By 'sql server timestamps', to what are you referring?
Some named fields using datetime datatypes. OR Some named fields using timestamp datatypes. Timestamp datatypes have NOTHING to do with date or time. If datetime datatypes, then use one of the date math functions, e.g., datediff(). Here is a simple use of timestamp datatype comparisons: (This should retrieve any entry made after the last entry for CustID = 2.) DECLARE @MyTable table ( RowID int IDENTITY , CustID int , MyData varchar(20) , EntrySeq timestamp ) INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 1, 'Test Data 1' ) INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 2, 'Test Data 2' ) INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test Data 3' ) INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 2, 'Test Data 4' ) INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test Data 5' ) SELECT RowID , CustID , MyData , EntrySeq FROM @MyTable WHERE EntrySeq > ( SELECT max( EntrySeq ) FROM @MyTable WHERE CustID = 2 ) -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <geir.for***@abeo.no> wrote in message news:1154614573.482758.293610@s13g2000cwa.googlegroups.com... > Hi! > > Is it possible to use comparisons sql server 2005 timestamps, like > operators greater than, less than on two sql server timestamps? > > We want to compare two fields in a record to see what is the latest, > and so on. If you know how, please give me an example on how to do it? > > Thank you. > Hello again and thanks for your responding.
Maybe, I was not clear enough to express what I meant. We use SQL timestamps to synchronize orders between two separate systems. To check to see if the order has been changed, we must compare on the sql server timestamp. If the order timestamp is equal in both systems, we know the orders are equal, too. But if they are not equal, we don't know which one has the latest changes. You may now understand what I meant to say. I looked in Books online and there it said: You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read. Well, maybe this approach won't work if the two systems may create their own timestamp just to determine if a row has changed inside their system... Regards Geir Arnie Rowland skrev: Show quote > You need to provide a bit more information. By 'sql server timestamps', to what are you referring? > > Some named fields using datetime datatypes. > OR > Some named fields using timestamp datatypes. > > Timestamp datatypes have NOTHING to do with date or time. > > If datetime datatypes, then use one of the date math functions, e.g., datediff(). > > Here is a simple use of timestamp datatype comparisons: (This should retrieve any entry made after the last entry for CustID = 2.) > > > DECLARE @MyTable table > ( RowID int IDENTITY > , CustID int > , MyData varchar(20) > , EntrySeq timestamp > ) > > INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 1, 'Test Data 1' ) > INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 2, 'Test Data 2' ) > INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test Data 3' ) > INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 2, 'Test Data 4' ) > INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test Data 5' ) > > SELECT > RowID > , CustID > , MyData > , EntrySeq > FROM @MyTable > WHERE EntrySeq > ( SELECT max( EntrySeq ) > FROM @MyTable > WHERE CustID = 2 > ) > > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > <geir.for***@abeo.no> wrote in message news:1154614573.482758.293610@s13g2000cwa.googlegroups.com... > > Hi! > > > > Is it possible to use comparisons sql server 2005 timestamps, like > > operators greater than, less than on two sql server timestamps? > > > > We want to compare two fields in a record to see what is the latest, > > and so on. If you know how, please give me an example on how to do it? > > > > Thank you. > > > ------=_NextPart_000_0AAD_01C6B6EC.1EF704C0 > Content-Type: text/html; charset=iso-8859-1 > Content-Transfer-Encoding: quoted-printable > X-Google-AttachSize: 4281 > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> > <META content="MSHTML 6.00.5296.0" name=GENERATOR> > <STYLE></STYLE> > </HEAD> > <BODY> > <DIV><FONT face=Arial size=2>You need to provide a bit more information. By 'sql > server timestamps', to what are you referring?</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>Some named fields using datetime > datatypes.</FONT></DIV> > <DIV><FONT face=Arial size=2>OR</FONT></DIV> > <DIV><FONT face=Arial size=2>Some named fields using timestamp > datatypes.</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>Timestamp datatypes have NOTHING to do with date or > time.</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>If datetime datatypes, then use one of the date > math functions, e.g., datediff().</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>Here is a simple use of timestamp datatype > comparisons: (This should retrieve any entry made after the last entry for > CustID = 2.)</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face="Courier New" size=2>DECLARE @MyTable table<BR> > ( RowID > int IDENTITY<BR> , > CustID int<BR> , > MyData varchar(20)<BR> , > EntrySeq timestamp<BR> )</FONT></DIV> > <DIV><FONT face="Courier New" size=2></FONT> </DIV> > <DIV><FONT face="Courier New" size=2>INSERT INTO @MyTable ( CustID, MyData ) > VALUES ( 1, 'Test Data 1' )<BR>INSERT INTO @MyTable ( CustID, MyData ) VALUES ( > 2, 'Test Data 2' )<BR>INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test > Data 3' )<BR>INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 2, 'Test Data 4' > )<BR>INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test Data 5' > )</FONT></DIV> > <DIV><FONT face="Courier New" size=2></FONT> </DIV> > <DIV><FONT face="Courier New" size=2>SELECT<BR> RowID > <BR> , CustID<BR> , MyData<BR> , > EntrySeq<BR>FROM @MyTable<BR>WHERE EntrySeq > ( SELECT max( EntrySeq > )<BR> > FROM > @MyTable<BR> > WHERE CustID = > 2<BR> > )<BR></FONT></DIV> > <DIV><BR><FONT face=Arial size=2>-- <BR>Arnie Rowland, Ph.D.<BR>Westwood > Consulting, Inc</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR>Most > experience comes from bad judgment. <BR>- Anonymous</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2><</FONT><A > href="mailto:geir.for***@abeo.no"><FONT face=Arial > size=2>geir.for***@abeo.no</FONT></A><FONT face=Arial size=2>> wrote in > message </FONT><A > href="news:1154614573.482758.293***@s13g2000cwa.googlegroups.com"><FONT > face=Arial > size=2>news:1154614573.482758.293610@s13g2000cwa.googlegroups.com</FONT></A><FONT > face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> Hi!<BR>> > <BR>> Is it possible to use comparisons sql server 2005 timestamps, > like<BR>> operators greater than, less than on two sql server > timestamps?<BR>> <BR>> We want to compare two fields in a record to see > what is the latest,<BR>> and so on. If you know how, please give me an > example on how to do it?<BR>> <BR>> Thank > you.<BR>></FONT></BODY></HTML> > > ------=_NextPart_000_0AAD_01C6B6EC.1EF704C0-- You answered your own question.
SQL Server timestamp datatype is just a sequentially incrementing value that indicates the order items were added to (or changed in) the database. There will be very little value in attempting to compare these between to different databases or two different servers. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <geir.for***@abeo.no> wrote in message news:1154684663.408854.122940@h48g2000cwc.googlegroups.com... > Hello again and thanks for your responding. > > Maybe, I was not clear enough to express what I meant. > > We use SQL timestamps to synchronize orders between two separate > systems. To check to see if the order has been changed, we must compare > on the sql server timestamp. If the order timestamp is equal in both > systems, we know the orders are equal, too. But if they are not equal, > we don't know which one has the latest changes. You may now understand > what I meant to say. > > I looked in Books online and there it said: > > You can use the timestamp column of a row to easily determine whether > any value in the row has changed since the last time it was read. If > any change is made to the row, the timestamp value is updated. If no > change is made to the row, the timestamp value is the same as when it > was previously read. > > > Well, maybe this approach won't work if the two systems may create > their own timestamp just to determine if a row has changed inside their > system... > > Regards Geir > > > Arnie Rowland skrev: >> You need to provide a bit more information. By 'sql server timestamps', >> to what are you referring? >> >> Some named fields using datetime datatypes. >> OR >> Some named fields using timestamp datatypes. >> >> Timestamp datatypes have NOTHING to do with date or time. >> >> If datetime datatypes, then use one of the date math functions, e.g., >> datediff(). >> >> Here is a simple use of timestamp datatype comparisons: (This should >> retrieve any entry made after the last entry for CustID = 2.) >> >> >> DECLARE @MyTable table >> ( RowID int IDENTITY >> , CustID int >> , MyData varchar(20) >> , EntrySeq timestamp >> ) >> >> INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 1, 'Test Data 1' ) >> INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 2, 'Test Data 2' ) >> INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test Data 3' ) >> INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 2, 'Test Data 4' ) >> INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test Data 5' ) >> >> SELECT >> RowID >> , CustID >> , MyData >> , EntrySeq >> FROM @MyTable >> WHERE EntrySeq > ( SELECT max( EntrySeq ) >> FROM @MyTable >> WHERE CustID = 2 >> ) >> >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> <geir.for***@abeo.no> wrote in message >> news:1154614573.482758.293610@s13g2000cwa.googlegroups.com... >> > Hi! >> > >> > Is it possible to use comparisons sql server 2005 timestamps, like >> > operators greater than, less than on two sql server timestamps? >> > >> > We want to compare two fields in a record to see what is the latest, >> > and so on. If you know how, please give me an example on how to do it? >> > >> > Thank you. >> > >> ------=_NextPart_000_0AAD_01C6B6EC.1EF704C0 >> Content-Type: text/html; charset=iso-8859-1 >> Content-Transfer-Encoding: quoted-printable >> X-Google-AttachSize: 4281 >> >> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> >> <HTML><HEAD> >> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> >> <META content="MSHTML 6.00.5296.0" name=GENERATOR> >> <STYLE></STYLE> >> </HEAD> >> <BODY> >> <DIV><FONT face=Arial size=2>You need to provide a bit more information. >> By 'sql >> server timestamps', to what are you referring?</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>Some named fields using datetime >> datatypes.</FONT></DIV> >> <DIV><FONT face=Arial size=2>OR</FONT></DIV> >> <DIV><FONT face=Arial size=2>Some named fields using timestamp >> datatypes.</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>Timestamp datatypes have NOTHING to do with >> date or >> time.</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>If datetime datatypes, then use one of the >> date >> math functions, e.g., datediff().</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>Here is a simple use of timestamp datatype >> comparisons: (This should retrieve any entry made after the last entry >> for >> CustID = 2.)</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face="Courier New" size=2>DECLARE @MyTable >> table<BR> >> ( RowID >> int IDENTITY<BR> , >> CustID int<BR> , >> MyData varchar(20)<BR> , >> EntrySeq timestamp<BR> )</FONT></DIV> >> <DIV><FONT face="Courier New" size=2></FONT> </DIV> >> <DIV><FONT face="Courier New" size=2>INSERT INTO @MyTable ( CustID, >> MyData ) >> VALUES ( 1, 'Test Data 1' )<BR>INSERT INTO @MyTable ( CustID, MyData ) >> VALUES ( >> 2, 'Test Data 2' )<BR>INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, >> 'Test >> Data 3' )<BR>INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 2, 'Test >> Data 4' >> )<BR>INSERT INTO @MyTable ( CustID, MyData ) VALUES ( 3, 'Test Data 5' >> )</FONT></DIV> >> <DIV><FONT face="Courier New" size=2></FONT> </DIV> >> <DIV><FONT face="Courier New" size=2>SELECT<BR> >> RowID >> <BR> , CustID<BR> , MyData<BR> , >> EntrySeq<BR>FROM @MyTable<BR>WHERE EntrySeq > ( SELECT max( EntrySeq >> )<BR> >> FROM >> @MyTable<BR> >> WHERE CustID = >> 2<BR> >> )<BR></FONT></DIV> >> <DIV><BR><FONT face=Arial size=2>-- <BR>Arnie Rowland, Ph.D.<BR>Westwood >> Consulting, Inc</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. >> <BR>Most >> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2><</FONT><A >> href="mailto:geir.for***@abeo.no"><FONT face=Arial >> size=2>geir.for***@abeo.no</FONT></A><FONT face=Arial size=2>> wrote >> in >> message </FONT><A >> href="news:1154614573.482758.293***@s13g2000cwa.googlegroups.com"><FONT >> face=Arial >> size=2>news:1154614573.482758.293610@s13g2000cwa.googlegroups.com</FONT></A><FONT >> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> >> Hi!<BR>> >> <BR>> Is it possible to use comparisons sql server 2005 timestamps, >> like<BR>> operators greater than, less than on two sql server >> timestamps?<BR>> <BR>> We want to compare two fields in a record to >> see >> what is the latest,<BR>> and so on. If you know how, please give me an >> example on how to do it?<BR>> <BR>> Thank >> you.<BR>></FONT></BODY></HTML> >> >> ------=_NextPart_000_0AAD_01C6B6EC.1EF704C0-- >  |
|||||||||||||||||||||||
Other interesting topics