date time stamp in sql

  • Thread starter Thread starter Rod Snyder
  • Start date Start date
R

Rod Snyder

I'm developing an app that is taking an ms access database upsized to sql
server 2k and it has a field date/time for the time stamp; access shows a
default value of Now() in the design view; I get errors trying that in SQL
Server. Anyone have any suggestions?
Rod
 
Hi Rod,

As you mentioned that you encountered the error when upsizing the access db
to sqlserver, would you provide the some detailed infos on the error?
What's the Error infos?

Also, are you using the upsizewizard of the access to upsize the database?
I've made a simple test that upsizing a small access db(with one table
which contains a columns typed date/time , with default value Now()) , the
result is that the "date/time" field in access will be mapped to the
"DateTime" type in sqlserver and the Now() intial function will replaced by
"GetDate()" function in sqlserver. Also, if I changed the DateTime to
timestamp , I'll get the following error:

Defaults cannot be created on columns of data type timestamp

which indicate that the timestamp can not set default value as the DateTime
type. Are your problem the same as the above? If so, I think we have to
use the DateTime type instead the timestamp.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

-------------------------------------------------------------------------
 
Hi Rod,

Some further information on the Timestamp type in SQLServer, here is the
description in document:
=========================
timestamp
timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database. timestamp is
used typically as a mechanism for version-stamping table rows. The storage
size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data
type defined in the SQL-92 standard. The SQL-92 timestamp data type is
equivalent to the Transact-SQL datetime data type.

A future release of Microsoft? SQL Server? may modify the behavior of the
Transact-SQL timestamp data type to align it with the behavior defined in
the standard. At that time, the current timestamp data type will be
replaced with a rowversion data type.
=========================

You may view the following full reference in MSDN:
#Transact-SQL Reference timestamp
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ta-tz_6fn4.asp?frame=true

So I think in SQLServer, if we want to store date/time data , we should use
the DateTime datatype rather than the timestamp. Hope these also help.
Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx
 
Back
Top