|
|
Posted by Erland Sommarskog on 06/20/06 22:24
Filips Benoit (benoit.filips@pandora.be) writes:
> Tables: COMPANY: COM_ID, COM_NAME, .....
> PROPERTY: PRP_ID, PRP_NAME, PRP_DATATYPE_ID, PRP_DEFAULT_VALUE
> ( nvarchar)
> COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUE
> (nvarchar)
>
> Use: Without adding new field the user can add new properties to the
> companies just by adding a new property in table PROPERTY and mapping
> the new property to a companie. CPRP_VALUE contains all kind of
> datatypes but is stored as text.
>
> Problem: when I query the database ( SP, views, etc) I have problems with
> floats and date bacause in the interface ( Access2000.adp) :
>> the float-format is 0,11 and in TSQL is 0.11
>>the date-format is DD/MM/YYYY and in TSQL it is YYYY-MM-DD
>
> Can I convert the data within the Stored Procedure for selecting and
> sorting on those fields.
>
> for example: How to sort on CPRP_VALUE containing floatvalues stored as
> nvarchar ??
convert(float, replace(CPRP_VALUE, ',', '.')
For datetime conversion, look at the topic for CAST and CONVERT in
Books Online. You need to use one of the format codes, but I don't
know them by heart.
> The client wants to stay with the comma-format because it is common-used
> here.
An alternative would be to use the data type sql_variant for CPRP_VALUE.
In that case, all values are stored in native format.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|