Is there any in built functions in SQL server to find whether the given value is integer or currency or date or decimal?
I want the in built functions to find int, currency and decimal.
susanthosh
2010-02-09 11:47:01
Paste some sample code, we can't work out what you want.
Codesleuth
2010-02-09 12:05:46
A:
If you're dealing with an SQL_VARIANT, use the built-in SQL_VARIANT_PROPERTY function:
SELECT SQL_VARIANT_PROPERTY(@value,'BaseType') AS 'Base Type'
Codesleuth
2010-02-09 11:46:40
A:
There are no real inbuilt functions like you are looking for. The only option to determine is to query sysobjects, syscolumns and systypes (SQL 2000).
select
obj.name,
col.name,
typ.name
From dbo.sysobjects obj
Inner Join dbo.syscolumns col
On col.id = obj.id
Inner Join dbo.systypes typ
On typ.xusertype = col.xusertype
This query will list all tables with their columns and their types. Currency will be of type money, decimal is numeric. Within the columns table you can find the precision and scale of numeric.
-1 for false statement, +1 for very useful answer. There are functions that does what he is looking for, as I stated in my answer. They just might not do everything he is looking for -- then your answer can help in a non-standard way.
Hogan
2010-02-09 13:29:28
IsDate I definitely have overseen, but please can you enlighten how IsNumeric is able to differentiate between a numeric, money and integer type?This is the original question and therefore I made the addition "...like you are looking for" as there is no function to do IsMoney, IsInteger or IsNumeric (sensing the numeric type only instead of verifying a number).
2010-02-09 17:48:24
But this will not look at data in a varchar field and tell you if is an integer. That is what most people asking this sort of question want to be able to do.
HLGEM
2010-02-11 19:22:49