SQL

timestamp datatype in sql

timestamp: Stores a unique number that gets updated every time a row gets created 
or modified. 

timestamp type in sql

SELECT GETDATE()    -- 2017-07-28 21:34:55.830
SELECT GETUTCDATE()     -- 2017-07-28 18:34:55.830
SELECT SYSDATETIME()        -- 2017-07-28 21:02:22.7446744
SELECT SYSUTCDATETIME()     -- 2017-07-28 18:20:27.5202777
SELECT SYSDATETIMEOFFSET()      -- 2017-07-28 21:02:22.7446744 +03:00
SELECT DAY(GETDATE())       -- 28
SELECT MONTH(GETDATE())     -- 7
SELECT YEAR(GETDATE())      -- 2017
SELECT DATENAME(month, GETDATE())       -- July
SELECT DATEADD(month, 2, '2017-7-28')       -- 2017-09-28 00:00:00.000
SELECT DATEADD(day, 5, '2017-7-28')     -- 2017-08-02 00:00:00.000
SELECT DATEADD(day, -5, '2017-7-28')        -- 2017-07-23 00:00:00.000
SELECT DATEDIFF(year, '2017-7-28', '2018-9-28')     -- разница 1 год
SELECT DATEDIFF(month, '2017-7-28', '2018-9-28')    -- разница 14 месяцев
SELECT DATEDIFF(day, '2017-7-28', '2018-9-28')      -- разница 427 дней
SELECT TODATETIMEOFFSET('2017-7-28 01:10:22', '+03:00')
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:30')
SELECT EOMONTH('2017-02-05')    -- 2017-02-28
SELECT EOMONTH('2017-02-05', 3) -- 2017-05-31
SELECT DATEFROMPARTS(2017, 7, 28)       -- 2017-07-28
SELECT ISDATE('2017-07-28')     -- 1
SELECT ISDATE('2017-28-07')     -- 0
SELECT ISDATE('28-07-2017')     -- 0
SELECT ISDATE('SQL')            -- 0
--Example:
CREATE TABLE Orders
(
    Id INT IDENTITY PRIMARY KEY,
    ProductId INT NOT NULL,
    CustomerId INT NOT NULL,
    CreatedAt DATE NOT NULL DEFAULT GETDATE(),
    ProductCount INT DEFAULT 1,
    Price MONEY NOT NULL
);
SELECT * FROM Orders
WHERE DATEDIFF(day, CreatedAt, GETDATE()) = 16