Search Code Shode

Friday, June 24, 2011

SQL Server DateTime Format Chart

SQL Server DateTime Formatting

Usually the formatting of a DateTime value into a more readable date and time is dealt with by the client application. However, there are some situations were it is useful to convert a DateTime into a formatted character string within a SQL statement.

Converting a DateTime to a VarChar

The Transact-SQL (T-SQL) Convert command can be used to convert data between different types. When converting a DATETIME value to a VARCHAR string value a style code may be applied. The following code uses style code 2 to indicate that an ANSI standard date (yy/mm/dd) should be used to represent the date as a string.
SELECT convert(DATETIME,convert(VARCHAR, getdate(),2))

DateTime Style Codes

The table below describes the most popular style codes that are available for use when converting between a DateTime and a character representation. Each example uses today's date, 8 September 2007.
Style CodeStyleFormatExample
0 or 100Default. Equivalent to not specifying a style code.mon dd yyyy hh:mmAMSep 8 2007 9:00PM
3UK / French date.dd/mm/yy08/09/07
5Italian date.dd-mm-yy08-09-07
6Abbreviated month.dd mmm yy08 Sep 07
7Abbreviated month.mmm dd, yySep 08, 07
8 or 10824 hour time.HH:mm:ss21:00:00
9 or 109Default formatting with seconds and milliseconds appended.mon dd yyyy hh:mm:ss:fffAMSep 8 2007 9:00:00:000PM
10USA date with hyphen
11Japanese date.yy/mm/dd07/09/08
12ISO date.yymmdd070908
13 or 113European default with seconds and milliseconds.dd mon yyyy HH:mm:ss:fff08 Sep 2007 21:00:00:000
14 or 11424 hour time with milliseconds.HH:mm:ss:fff21:00:00:000
20 or 120ODBC canonical date and time.yyyy-mm-dd HH:mm:ss2007-09-08 21:00:00
21 or 121ODBC canonical date and time with milliseconds.yyyy-mm-dd HH:mm:ss.fff2007-09-08 21:00:00.000
101USA date with
102ANSI date with
103UK / French date with century.dd/mm/yyyy08/09/2007
104German date with
105Italian date with century.dd-mm-yyyy08-09-2007
106Abbreviated month with century.dd mmm yyyy08 Sep 2007
107Abbreviated month with century.mmm dd, yyyySep 08, 2007
110USA date with hyphen separators and
111Japanese date with century.yyyy/mm/dd2007/09/08
112ISO date with century.yymmdd20070908
126ISO8601, for use in XML.yyy-mm-ddThh:mm:ss2007-09-08T21:00:00

Friday, June 10, 2011

SQL Query to Find Number of Rows, Columns, ByteSize for each table in the Database

table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
COUNT(*) AS col_count,
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

Create, Alter & Drop Stored Procedures using C#

I always wonder how can one Create, Alter or Drop Stored Procedures using C# and personal user interface. I came up with the solution using SQL Server Management Objects (SMO). It is quite simple using SMOs.

I got a situation in which I usually need to Create & Alter same Stored Procedures on several SQL Servers simultaneously. It was a hectic job to connect each server and then execute the script for creating or altering SP. Solution was quite simple. Below is the step wise solution to the problem.

I named this solution as Stored Procedure Management

Step 1:
Add reference to:

To Add Reference to given Assemblies, right click your solution and select 'Add reference' and then browse and select the following dlls:

C:\Program Files\Microsoft SQL Server\XXX\SDK\Assemblies\Microsoft.SqlServer.SMO.dll
C:\Program Files\Microsoft SQL Server\XXX\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
C:\Program Files\Microsoft SQL Server\XXX\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.sfc.dll 

*where  XXX is the SQL Server version number (90 or 100).

Step 2:
There should be a Data Source which would provide database credentials for your desired database server(s).

In this example I am retrieving this information from a common admin database server as I am going to use this application to Create/Alter SPs on multiple servers.