Softminer.net

This is the list of Dateformat in SQL 2008 with examples

Data type	Output
Time	12:35:29. 1234567
Date	2007-05-08
Smalldatetime	2007-05-08 12:35:00
Datetime	2007-05-08 12:35:29.123
datetime2	2007-05-08 12:35:29. 1234567
Datetimeoffset	2007-05-08 12:35:29.1234567 +12:15

as you see the new Datetimeoffset is useful for timezone in different countries.

Timestap is changed to rowversion (both are valid)

timestap Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows.

if you read a row and wants to edit it check the timestamp in update, if the timestamp is changed means during the update this row is changed by someone else.

furthermore, if you want to have a datetime column which automatically fills in insert and update by sql you should make a default value to GETDATE() function.

this default value will fullfill the insert and for update you need to add a trigger like this

CREATE TRIGGER KeepUpdated on Profiles
FOR UPDATE, INSERT AS
UPDATE dbo.Profiles
SET LastUpdate = GetDate()
WHERE Username IN (SELECT Username FROM inserted)