Posted on 6:15 AM by Softminer and filed under

This is the list of Dateformat in SQL 2008 with examples

Data typeOutput
Time12:35:29. 1234567
Smalldatetime2007-05-08 12:35:00
Datetime2007-05-08 12:35:29.123
datetime22007-05-08 12:35:29. 1234567
Datetimeoffset2007-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
UPDATE dbo.Profiles
SET LastUpdate = GetDate()
WHERE Username IN (SELECT Username FROM inserted)
Responses to ... Datetime in SQL 2008