SQL Snippets

Rounding off to 2 decimal places

cast(round([value],2) as decimal(18,2))

Compare only Year from datetime field

select * from [table_name] 
where year(date_time_field) = year(getdate())

check if a SQL server string is null or empty

ISNULL(NULLIF(text, ''), '') AS Offer_Text

The differences between LEN and DATALENGTH in SQL Server
LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

DATALENGTH
Returns the number of bytes used to represent any expression.

Comma separated non null values

STUFF(COALESCE(', ' + value1, '') + COALESCE(', ' + value2, '') + COALESCE(', ' + value3, '') + COALESCE(', ' + value4, ''), 1, 2, '') AS [FIELD_NAME]

Comparing Month and Year

month(datefield) = MONTH(GETDATE())
and year(datefield) = year(GETDATE())

CAST to DATETIME

Cast('7/7/2011' as datetime)

How to force a SQL Server 2008 database to go Offline

USE master
GO
ALTER DATABASE YourDatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

USE master
GO
ALTER DATABASE YourDatabaseName
SET ONLINE
GO

SQL Server Disabling All Triggers

sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

How to turn IDENTITY_INSERT on and off

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT sometableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

Add counter column to table

select row_number() over (order by (select 0)) as number, * from TABLE

Time in HH:MI:SS format

SELECT CONVERT(varchar, getdate(), 108)