mapping data types

I am building a .NET app that uses sqlDataReader.GetSchemaTable method. this
returns a table which includes the column "Provider Type" which is the
database data type of the column, however it is a numeric value, are the
mappings between these numeric values and the textual data type names
documented anywhere?
eg Money appears to be 9
and Date appears to be 15For SQL Server 2000, the systypes system table contains mappings between the
data type name and a number called 'xtype'. They don't seem to line up with
your observations though.
xtype Data type name
-- --
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar
"guy" wrote:

> I am building a .NET app that uses sqlDataReader.GetSchemaTable method. th
is
> returns a table which includes the column "Provider Type" which is the
> database data type of the column, however it is a numeric value, are the
> mappings between these numeric values and the textual data type names
> documented anywhere?
> eg Money appears to be 9
> and Date appears to be 15
>|||Mark,
hmmm interesting, so far i have found:-
2 bit
3 char
6 float
8 Int
9 money
15 smalldatetime
16 smallint
22 varchar
cheers
"Mark Williams" wrote:
> For SQL Server 2000, the systypes system table contains mappings between t
he
> data type name and a number called 'xtype'. They don't seem to line up wit
h
> your observations though.
> xtype Data type name
> -- --
> 34 image
> 35 text
> 36 uniqueidentifier
> 48 tinyint
> 52 smallint
> 56 int
> 58 smalldatetime
> 59 real
> 60 money
> 61 datetime
> 62 float
> 98 sql_variant
> 99 ntext
> 104 bit
> 106 decimal
> 108 numeric
> 122 smallmoney
> 127 bigint
> 165 varbinary
> 167 varchar
> 173 binary
> 175 char
> 189 timestamp
> 231 sysname
> 231 nvarchar
> 239 nchar
>
> "guy" wrote:
>

Manually Inserting Data in DateTime Field

Using Server Explorer in VB 2005, I am manually entering data in a table in a SQL Server 2005 Express database that includes a DateTime field. I have tried every conceivable format, but no matter what I try get one of these 2 errors:

1. String was not recognized as valid DateTime

2. Operand type class; text incompatible with DateTime

I have Googled this to death, but no example which involves trying to enter the data manually, say from Server Explorer.

Formats tried include all datetime formats (mmddyy, yymmdd, using dashes or slashes, enclosing in single quotes or pound signs).

I would appreciate if someone could please give me an example that I can literally insert without error.

hi,

I tried myself inserting via the VS designer the following:

2006-09-19 (it works)
2006/09/19 (it works)
19/09/2006 (it works)
19-09-2006 (it works)

the 2 last entries are dangerous as the server could misunderstand the datetime format... my pc has the italian locale set, so it could be ok, but I do advise to use a "general" ISO format that can not bring to misunderstanding, thus YYYY-MM-DD

regards

|||

Andrea, appreciate the response, but I tried your suggestions, and some related ones (09/19/2006), and they did not work. The error was Operand type clash; text incompatible with DateTime.

Again, I am opening the SQL Server 2005 Express database, then the table, from Server Explorer, and then choosing Show Table Data from the shortcut menu, and then typing the data into the datetime column field. Any further ideas would be appreciated.

|||I am an idiot. I was typing the data into the wrong field. Your suggestions work. Sorry for wasting your time.