|
|
Posted by Filips Benoit on 06/21/06 07:07
Thanks,
Float-convertions is OK
Can't find a code for datetime-conversion from nvarchar format DD/MM/YYYY
http://doc.ddart.net/mssql/sql70/ca-co_1.htm
What is the max lenght of sql_variant? Now CPRP_VALUE = nvarchar 1024
Is it possible to change the field-datatype having existing data?
Filips
Sorry, I first repied to you iso to the group.
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97E941894BCFYazorman@127.0.0.1...
> 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]
|