{"id":68,"date":"2006-04-20T11:29:55","date_gmt":"2006-04-20T10:29:55","guid":{"rendered":"http:\/\/www.galhano.com\/blog\/?p=68"},"modified":"2006-04-20T12:00:29","modified_gmt":"2006-04-20T11:00:29","slug":"timestamps","status":"publish","type":"post","link":"http:\/\/galhano.com\/blog\/?p=68","title":{"rendered":"SQL Server: TIMESTAMPS"},"content":{"rendered":"

The SQL Server timestamp data type has nothing to do with times or dates<\/font>.<\/font> SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.<\/font><\/p>\n

It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.<\/font><\/p>\n

I’d suggest using a DATETIME or SMALLDATETIME column in this case. DATETIME columns can store dates from January 1st, 1753 through December 31st, 9999 (there’s that Y10K problem) and are accurate to roughly 3 milliseconds. They use 8 bytes of storage. SMALLDATETIME columns can store dates from January 1st, 1900 through June 6th, 2079 and are accurate to the minute. SMALLDATETIME columns only use 4 bytes of storage.<\/font><\/p>\n

You can insert values into DATETIME columns (or SMALLDATETIME) columns by enclosing them in quotes.
\n<\/font><\/p>\n\n\n
\n

INSERT Table1 (DateTimeColumn)
\nVALUES (‘6\/3\/2021’)<\/font><\/td>\n<\/tr>\n<\/table>\n


\nThis will insert the date part with the time set to midnight (12:00:00 AM). You can insert the current system date and time using the GETDATE() function:
\n<\/font><\/p>\n\n\n
\n

INSERT Table1 (DateTimeColumn)
\nVALUES ( GETDATE() )<\/font><\/td>\n<\/tr>\n<\/table>\n

\n

\n

Your SELECT statement from above might look something like this:<\/font><\/p>\n

\n\n\n
\n

SELECT * FROM links WHERE gdate = \t\t‘2000-11-05’<\/font><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

\n

\n

This will run fine if you are putting dates in with no times. If you are adding times and want all the records for a particular day you can do something like this:
\n<\/font><\/p>\n\n\n
\n

SELECT * FROM links WHERE LEFT( \t\tCONVERT(varchar, gdate, 120), 10) = ‘2000-11-05’<\/font><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

<\/font><\/p>\n

—————————<\/p>\n

Usando DATETIME para assumir por defeito o valor dataHora actual, pode-se colocar no DefaultValue a funcao (getdate())<\/p>\n","protected":false},"excerpt":{"rendered":"

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. It further states Never use timestamp columns […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-68","post","type-post","status-publish","format-standard","hentry","category-sql-server","author-admin"],"_links":{"self":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/68","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=68"}],"version-history":[{"count":0,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/68\/revisions"}],"wp:attachment":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}