How to return RFC822 compliant dates?

You can use the following UDF to return RFC822 compliant dates. It is specilly usefull to return dates that will be used in RSS feeds.


CREATE FUNCTION [dbo].[fn_RFC822Date]
(
@Date datetime
)
RETURNS nvarchar(70)
AS
BEGIN
RETURN(LEFT(DATENAME(dw, @Date),3) + ', ' + STUFF(CONVERT(nvarchar,@Date,113),21,4,' GMT'))
END

Try this in a Query-Command

select dbo.fn_RFC822Date(getdate())

It will return the actual date in the RFC822 format.

Example: Mon, 23 Jul 2007 16:00:18 GMT

Split function

Many times we need to deal with lists in T-SQL. For instance we need to have a list of values to pass it to IN clause in SQL. This list is supposed to look like delimited string. However we can not pass it to T-SQL procedure as a string because T-SQL compiler can not interpret delimited string as a list of values. In MSSQL the list of values is of table type, so we need to have a tool to convert delimited string to table. The function below does just that. So we are able to pass delimited strings to the procedure, convert this string to table and use it later on.

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
While (Charindex ( @SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))
Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END

How to get a number of repeating chars in a string?

The number of repeating chars in a string can be calculated by using the string length minus the length of the string were was replaced the char that we want to count.

Try this in a Query-Command:

DECLARE @str varchar(50)
SET @str = 'Hello World'
DECLARE @delimiter varchar(50)
SET @delimiter = 'l'

SELECT len(@str) - len(replace(@str, @delimiter, ''))


It will display '3' as result.

How to get a random row from a table?

To get a random row from a table you can use the function newid().

select top 1 columns....
from table
order by newid()

Or, if you want to choose a random row between first the 20 rows

set ROWCOUNT 20
select top 1 *
from (Select * from table)
order by newid()
set ROWCOUNT 0

T-SQL code to get the last day of the month!

The logic behind this 'last day of the month' calculations is following: a) get the month part of a given date; b) add one month to it to get the next month; c) then subtract 1 day from the first of that month. This can be written in one line:

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))

SET NOCOUNT ON statement

Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement. This can help to reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

Complex Sequencing

CASE statements can be used in an ORDER BY clause to order non-sequential data. This example sorts stores by name, but puts all the stores in California at the top of the list.

SELECT stor_name, state
FROM stores
ORDER BY CASE state WHEN 'CA' then 1 ELSE 2 END, stor_name

Calculate the number of business days between two dates

You can use the following T-SQL scrip to calculate the number of business days between two dates. Business days usually are defined as non-weekend days. Popular responses include loops or temporary tables. But here is a single-expression approach that does the job.

DECLARE @DateOld datetime, @DateNew datetimeSET @DateOld = '10-Sep-2005'SET @DateNew = GETDATE()
SET DATEFIRST 1
SELECT DATEDIFF (day, @DateOld, @DateNew) - (2 * DATEDIFF(week, @DateOld, @DateNew)) - CASE WHEN DATEPART(weekday, @DateOld + @@DATEFIRST) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, @DateNew + @@DATEFIRST) = 1 THEN 1 ELSE 0 END

Getting column list into a variable

The easiest way of getting a column list into a variable is to assign the contents of sys.columns. Create a variable to hold the column list and then reassign it in the select statement. To get the column key use the object id of the object.

We are using the fact that coalesce returns the first non null value here so that we get a ',' for each of the subsequent field names but just the column name for the first field. It's much easier than using a cursor on sys.columns to get each field.

create table temptable(id int identity, scol1 varchar(30), scol2 varchar(30))

Declare @myvar varchar(max)
set @myvar = ''

select @myvar = coalesce(@myvar + name + ',','')
from sys.columns
where object_id = object_id('temptable')
order by column_id

select @myvar

drop table temptable

You will get as result: 'id,scol1,scol2,'

How to find some text inside the Stored Procedures - Tips for T-SQL on SQL Server

Sometimes when you have a database with a lots of Stored Procedures is not practical to check one by one to find a particular comment or a piece of code. One way to do this easily is to search for a particular string inside the Stored Procedures using the system table sys.procedures and the object OBJECT_DEFINITION.

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Dark Vader%'

It will return a list with the names of the Stored Procedures where the string 'Dark Vader' is present.