|
|
Posted by Madhivanan on 06/21/06 09:04
>>Can't find a code for datetime-conversion from nvarchar format DD/MM/YYYY
Do the formation at client application or read about Convert function
in sql server help file
Madhivanan
Filips Benoit wrote:
> 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]
|