Small heaps of code

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

===== Cheat sheet for MSSQL 2008 =====
Get table names from database

<code sql>
select table_name from information_schema.tables
</code>
Show table structure

<code sql>
exec sp_columns <tablename>
</code>
Limit output to x lines

<code sql>
Select top <x> * from <tablename
</code>


===== Dates and times =====

==== Date conversion ====

The magic number in the convert function determines the datestyle: 120 == ODBC canonical.

More about converting [[http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx][here]]

Select events that are active at a certain time:
<code sql>
SELECT event, starttime, endtime
FROM table
WHERE  convert(datetime,'2012-08-04 12:00:00',120) BETWEEN starttime and endtime
</code>

===== Python and mssql =====
http://code.google.com/p/pymssql/wiki/PymssqlExamples



===== Caveats =====
Error message: "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier."

can be resolved by adding **tds_version = 8.0** to your odbc.ini

(the Why is nicely summarised e.g. by Ubuntitis http://www.ubuntitis.com/?p=64)

Small heaps of code

User Tools

Site Tools

Page Tools