Freek's Blog

I was inserting my datetime values from sql server 2000 to a column in a sql server 2008 database.
The result was:

This was a not nullable field. Because there is (I verified this) always a filled field.

When I made it nullable, the insert processed with no problems.
And ALL the fields where filled with datetime values.

Is this a bug?

or am I missing a conversion here?

I needed to know what kind of datatype a certain column was.
By opening the table in object explorer is one way to do it.

I found another (easier?)  way of doing it.

1. first create and empty temp table
2. fill this table with the desired table using sp_columns
3. retrieve the values.

--create table

create table #ColumnInfo(
TABLE_QUALIFIER sysname null,
TABLE_OWNER sysname null,
TABLE_NAME sysname null,
COLUMN_NAME sysname null,
DATA_TYPE sysname null,
TYPE_NAME sysname null,
[PRECISION] int null,
LENGTH int null,
SCALE int null,
RADIX int null,
NULLABLE bit null,
REMARKS nvarchar(4000) Null,
COLUMN_DEF sysname null,
SQL_DATA_TYPE int null,
SQL_DATETIME_SUB int null,
CHAR_OCTET_LENGTH int null,
ORDINAL_POSITION int null,
IS_NULLABLE char(3) null,
SS_DATA_TYPE int null)

--fill table

insert into #ColumnInfo
exec sp_columns 'table_name'

--retrieve values

select column_name, type_name, [precision], length, nullable
from #ColumnInfo where column_name like '%column_name%'

example result:
column_name             type_name                precision           length             nullable
ID                                int identity                   10                     4                      0
etc