SQL Snippets

Author: | Categories: Database, Programming No Comments

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)

Leave a Reply

Your email address will not be published.