0

For example if a column has value like 200806, and I want to know if this variable is a date variable or a numeric variable or a character variable, where can I see that? Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jenny Shu
  • 47
  • 2
  • 4
  • 12
  • HUH??? You first say you have a column and then say it is a variable. Either way, you look at the datatype of the column or variable to know what datatype it is. – Sean Lange Dec 23 '15 at 15:33
  • If you're referring to ta column data type, try this... http://stackoverflow.com/questions/13405572/sql-statement-to-get-column-type – FormulaChris Dec 23 '15 at 15:35
  • If you want to actually manipulate the possible data type of value then it is hard to do as "20150702" qualifies both as integer or date.. & of course, it could be string also... to know the data type from table you can always use **SELECT * FROM Information_Schema.Columns WHERE Table_Name ='your table name'** – Anuj Tripathi Dec 23 '15 at 15:37
  • you can use sql_variant_property function to know properties like basetype,precision,scale,totalbytes,collation and maxlength. It is applicable for both variables and Table columns.Check one of my blog post reg it - https://arulmouzhi.wordpress.com/2020/03/01/how-to-know-the-datatype-and-properties-of-a-variable-and-columns-of-a-table/ – Arulmouzhi Mar 01 '20 at 17:50

4 Answers4

2

You can pull the column information for your table by querying the INFORMATION_SCHEMA.COLUMNS table:

SELECT  DATA_TYPE 
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_CATALOG = 'YourDatabase'
AND     TABLE_SCHEMA = 'YourSchema'
AND     TABLE_NAME = 'YourTable'
AND     COLUMN_NAME = 'YourColumn'
Siyual
  • 16,415
  • 8
  • 44
  • 58
1

You can manipulate actual data type from a column by using TRY_PARSE in SQL Server to test if requested data type is allowable or not ; below is a sample code for that. As you can see that value 200806 is parsed as date

SELECT
        TRY_PARSE('200806' AS DECIMAL )  AS 'decimal check',
        TRY_PARSE('200806' AS INTEGER)  AS 'integer Check',
        TRY_PARSE('200806' AS DATE )  AS 'date Check'

You might also want to know about TRY_CAST or TRY_CONVERT here but they are difference in terms of SQL version and compatibility level [you need to have 110 compatibility to execute that (i.e. SQL Server 2012+)]. Try this

SELECT
    TRY_CONVERT(decimal,'200806'  )  AS 'decimal check',
    TRY_CONVERT(INT,'200806' )  AS 'integer Check',
    TRY_CONVERT(DATE,'200806' )  AS 'date Check'

If you always consider INFORMATION_SCHEMA for gathering metadata information from SQL Server.

Anuj Tripathi
  • 2,251
  • 14
  • 18
0

If you have SSMS (SQL Server Management Studio) then you can expand Database -> Tables -> Columns and find the type of that column.

Else, use sp_help (or) sp_helptext along with your table name like below 

sp_help your_table_name
Rahul
  • 76,197
  • 13
  • 71
  • 125
0

Try This @jenny

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable' AND COLUMN_NAME = 'yourColumn'
khaled4vokalz
  • 876
  • 9
  • 13