SQL Server: Date Formats

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
Result : 2009/03/05

SELECT CONVERT(VARCHAR(10), GETDATE(), 11) AS [YY/MM/DD]
Result : 09/03/05

SELECT CONVERT(VARCHAR(30), GETDATE(), 131)as Datetime
Result : 9/03/1430 2:05:32:957PM

SELECT CONVERT(VARCHAR(22), GETDATE(), 100)as datetime
Result : Mar 5 2009 2:06PM

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
Result :03/05/2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
Result :03-05-09

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
03-05-2009

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
Result :Mar 05, 09

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
Result :Mar 05, 2009

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
Result :05 Mar 09

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
Result :05 Mar 2009


SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
Result :05.03.09

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
Result :05.03.2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
Result :05-03-09


SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
Result :05-03-2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
Result :05/03/09

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
Result :05/03/2009

SQL Server 2005 : Split Function

CREATE FUNCTION dbo.Splitter(@strList VARCHAR(8000),@strSeperator VARCHAR(10))
Returns @tblSplit TABLE (splitValue VARCHAR(255))
AS
BEGIN
DECLARE @tmp_FnCommaSplitter TABLE (splitValue VARCHAR(255))
DECLARE @strTmp VARCHAR(5000)
DECLARE @pos INT
DECLARE @lenSeperator INT
SELECT @lenSeperator = LEN(@strSeperator)
SELECT @strTmp = @strList
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
WHILE (@pos <> 0)
BEGIN
IF SUBSTRING(@strTmp,1,@pos-1) <> ''
INSERT @tmp_FnCommaSplitter VALUES (SUBSTRING(@strTmp,1,@pos-1))

SELECT @strTmp = SUBSTRING(@strTmp,@pos+@lenSeperator,5000)
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
END
IF @strTmp <> ''
INSERT @tmp_FnCommaSplitter VALUES (LTRIM(RTRIM(@strTmp)))
INSERT @tblSplit SELECT * FROM @tmp_FnCommaSplitter
RETURN
END

SQL Datechecking

DECLARE @datestring varchar(8)
SET @datestring = '12/21/98'
SELECT ISDATE(@datestring)

Procedure to get the record Count instead of using count(*)

sp_spaceused '' for getting the counts.
This will be faster instead of using select count(*)

Gridview Manipulations like ADD,DELETE,UPDATE:

Gridview Operations:

http://www.pritambaldota.com/Articles/Article9.aspx

Cleaning the Transaction Logs in SQL Server2005

To Back up the transaction log file :
BACKUP LOG TO DISK = ''
Eg.
BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

To Shrink the transaction log file:

DBCC SHRINKFILE (, ) WITH NO_INFOMSGS

How to clean the transaction Log in SQL Server

use master
go
dump transaction with no_log
go
use
go
DBCC SHRINKFILE (, 100) -- where 100 is the size you may want to shrink it to in MB, change it to your needs
go
-- then you can call to check that all went fine
dbcc checkdb()

(or)

To Truncate the log file:

Backup the database
Detach the database, either by using Enterprise Manager or by executing : *Sp_DetachDB [DBName]*
Delete the transaction log file. (or rename the file, just in case)
Re-attach the database again using: *Sp_AttachDB [DBName]*
When the database is attached, a new transaction log file is created.
To Shrink the log file:

Backup log [DBName] with No_Log
Shrink the database by either:

Using Enterprise manager :- Right click on the database, All tasks, Shrink database, Files, Select log file, OK.

Using T-SQL :- *Dbcc Shrinkfile ([Log_Logical_Name])*

You can find the logical name of the log file by running sp_helpdb or by looking in the properties of the database in Enterprise Manager.