The SQL Server cast function is the easiest data type conversion function to be used compared to the CONVERT function. It takes only one parameter followed by the AS clause to convert a specified value. A quick example is the following: [cc lang=”sql”] SELECT UserID_String = CAST(UserID AS varchar(50)) FROM dbo.User [/cc] This example will convert the integer to a character value. So what is the use of this? Well, more likely that not if you’re on this page you know the use, however it is usually used to concatenate or perform string like comparisons on numeric values, or to convert a numeric or string value in order to compare. An example is when trying to manually construct date parts into a custom format, you need to append the number comprising the date into a string. Say you wanted to make the date 2010/06/05 into a custom format of: 20100605. In order to do so, you need to utilize the datepart function then concatenate the results. The problem is, when you try to concatenate the results of datepart, instead of concatenating, the number get summed together. To get around this, simply use CAST. [cc lang=”sql”] DECLARE @mydate as DATETIME SET @mydate = GETDATE() — wrong way SELECT DATEPART(yy, @mydate) + DATEPART(mm, @mydate) + DATEPART(dd, @mydate) — output: 2020 — correct way SELECT CAST(DATEPART(yy, @mydate) AS VARCHAR(10)) — append a zero before the date so we get two digits on a one digit month + RIGHT(‘0’ + CAST(DATEPART(mm, @mydate) AS VARCHAR(10)), […]
Continue reading ...
To get the session ID, simply use the intrinsic variable @@SPID: [cc lang=”sql”] SELECT @@SPID [/cc] The acronym for SPID means Server Process ID. It is synonymous with session.
Continue reading ...
There are many good uses of the SELECT TOP 1 method of querying. Essentially, the select top 1 method is used to find the min or max record for a particular column’s value. There is some debate as to whether this is the ‘correct’ method of querying, however it should be known that this method does not break any guidelines and is supported by all standards of SQL. The TOP 1 means to only return one record as the result set. which record is returned, depends on the column that is specified in the order by clause. If you want to find the record with the minimum value for a particular column, you would query the record with the ORDER BY being ascending (ASC). If you want to find the maximum record with that value, you would query it with the ORDER BY descending (DESC). For example, say you wanted to find the record in a table for a customer that has the largest order. Traditionally, the way to do this would be to find the maximum order value then join back on the table in order to find the record. Like the following: [cc lang=”sql”] SELECT * FROM SalesLT.SalesOrderHeader soh WHERE TotalDue = ( SELECT MAX(TotalDue) FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = soh.SalesOrderID ) [/cc] However, using the SELECT..TOP 1 method of querying, you can perform the same query using the following notation. [cc lang=”sql”] SELECT TOP 1 * FROM SalesLT.SalesOrderHeader ORDER BY TotalDue DESC [/cc] One of the major […]
Continue reading ...
The maximum value for a smallint in SQL Server is: -32768 through 32767 And the byte size is: 2 bytes other maximum values: BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes) Int: -2147483648 through 2147483647 (4 bytes) TinyInt: 0 through 255 (1 byte)
Continue reading ...
The maximum value for a tinyint in SQL Server is: 0 through 255 And the byte size is: 1 byte other maximum values: BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes) Int: -2147483648 through 2147483647 (4 bytes) SmallInt: -32768 through 32767 (2 bytes)
Continue reading ...
