Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
roslen
Starting Member
5 Posts |
 Posted - 2008-03-06 : 00:28:29
|
| I need to know how to determine the data type of each column in any resultset. The resultset is the output of *any* SELECT query. I have only gone as far as knowing the column names.Please help. Thanks. |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-03-06 : 01:45:52
|
| Check the syscolumns tableJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-06 : 02:02:34
|
| I don't think that syscolumns or any system table or view will help as you can convert the data type of the column in the result set. So I could have a column named ID that is of type int, syscolumns will show int, but I could have done this: SELECT CONVERT(varchar(5), ID) + '000' FROM MyTable. In my result set, the data type is now varchar.I don't think it's possible to know what data types each of the columns in the output is.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
roslen
Starting Member
5 Posts |
Posted - 2008-03-06 : 03:10:00
|
Thanks Jack for pointing out syscolumns, and thanks Tara for your response. I appreciate your ideas.  Meantime, my *solution* to this problem is to determine the data type of the resultset column at the application level. It's not pretty but at least I get it to work.I had this problem because I have a PHP script that processes SELECT queries and then outputs an HTML table whose cell values are formatted according to the corresponding data type, i.e., 'MM/DD/YYYY' for date values, 'TRUE/FALSE' for binary values, 'x,xx0.00' for floats, etc.Thanks All. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-03-06 : 08:14:48
|
| Then it is better to do it at application level. You have lot of functions thereAlso cant you make use column's datatype property of your recordset to determine the data type?ex (somethink like this)rs(0).datatypeMadhivananFailing to plan is Planning to fail |
|
|
|
roslen
Starting Member
5 Posts |
Posted - 2008-03-06 : 19:45:56
|
| I have been looking for something like that in PHP. Thanks. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-03-07 : 03:12:46
|
quote:
Originally posted by roslen
I have been looking for something like that in PHP. Thanks.
Post the solution here if found MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
data types of columns in resultsets