Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax

Using CAST:

CAST ( expression AS data_type )

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Principais requisitos minimos:

2012

SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR)
+ '-'
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR)
+ ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR)
+ ')'

SQL Server 7.0, 2000, and 2005, running the following will extract ONLY the version information.

And the following query will work on SQL Server 2000 and up:

read more »

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 in keys, especially primary keys, because the timestamp value changes every time the row is modified.

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.

You can insert values into DATETIME columns (or SMALLDATETIME) columns by enclosing them in quotes.

This 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:

Your SELECT statement from above might look something like this:

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:

—————————

Usando DATETIME para assumir por defeito o valor dataHora actual, pode-se colocar no DefaultValue a funcao (getdate())

Built-in data types SQL Server 2000 ships with 27 built-in (system) data types

User-defined data types

SQL Server 2000 supports user-defined data types too. User-defined data types provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. Using user-defined data type can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type. The user-defined data types are based on the system data types and can be used to predefine several attributes of a column, such as its data type, length, and whether it supports NULL values. To create a user-defined data type, you can use the sp_addtype system stored procedure or you could add one using the Enterprise Manager. When you create a user-defined data type, you should specify the following three properties:

• Data type’s name.
• Built-in data type upon which the new data type is based.
• Whether it can contain NULL values.

The following example creates a user-defined data type based on money data type named cursale that cannot be NULL:

EXEC sp_addtype cursale, money, 'NOT NULL' GO

Both system and user-defined data types are used to enforce data integrity. It is very important that we put forth a lot of effort while designing tables: the better you design your tables, the more time you can work without any performance problems. In an ideal case, you never will update the structure of your tables.