Convert YYYYMM to MMMYY
Use the DATENAME & SUBSTRING functions, like this: declare @str nvarchar(50) = ‘201604’ select UPPER(left(datename(mm,cast(@str+’01’ as date)),3))+substring(@str,3,2) –APR16
tsql
Subtraction between two sql queries
You should be able to use subqueries for that: SELECT (SELECT COUNT(*) FROM … WHERE …) – (SELECT COUNT(*) FROM … WHERE …) AS Difference Just tested it: Difference ———– 45 (1 row(s) affected)
SQL Server 2008 thousands separator for a column
SELECT FORMAT(12345,’#,0.00′); SELECT FORMAT(TotalArea,’#,0.00′) from table; Reference: https://msdn.microsoft.com/en-us/library/ee634206(v=sql.105).aspx
How to use group by with union in T-SQL
You need to alias the subquery. Thus, your statement should be: Select Z.id From ( Select id, time From dbo.tablea Union All Select id, time From dbo.tableb ) As Z Group By Z.id
How do I add the identity property to an existing column in SQL Server
I don’t beleive you can do that. Your best bet is to create a new identity column and copy the data over using an identity insert command (if you indeed want to keep the old values). Here is a decent article describing the process in detail: http://www.mssqltips.com/tip.asp?tip=1397
SQL Server – NOT IN
It’s because of the way NOT IN works. To avoid these headaches (and for a faster query in many cases), I always prefer NOT EXISTS: SELECT * FROM Table1 t1 WHERE NOT EXISTS ( SELECT * FROM Table2 t2 WHERE t1.MAKE = t2.MAKE AND t1.MODEL = t2.MODEL AND t1.[Serial Number] = t2.[serial number]);
revoke vs deny : what is the difference
Each object has a list of rules DENYing and GRANTing access. REVOKE is an operation that removes a rule from the list of access rules.
Getting offset of datetimeoffset in SQL Server
The DATEPART function has a tz option which returns the value’s UTC offset in int minutes. DATENAME also supports the tz option and returns the formatted UTC offset in the form [+-]HH:MM Note that a datetimeoffset value only contains a scalar UTC offset value, it does not contain any time-zone information at all (as multiple … Read more
MS SQL server casting without exception
This will default non-numerics to 0 and will not require another statement: SELECT CASE WHEN ISNUMERIC(myvarcharcolumn)=1 THEN CONVERT(float, REPLACE(LTRIM(RTRIM(myvarcharcolumn)), ‘,’, ‘.’)) ELSE 0 END AS myfloatcolumn The REPLACE() function call is used to change commas to periods. Commas are used in some cultures as a decimal separator (e.g., “1,25” instead of “1.25”), but unless your … Read more
Set variable with multiple values and use IN [duplicate]
You need a table variable: declare @values table ( Value varchar(1000) ) insert into @values values (‘A’) insert into @values values (‘B’) insert into @values values (‘C’) select blah from foo where myField in (select value from @values)