Datatype-convertion in TSQL

Dear all,

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 ??

The client wants to stay with the comma-format because it is common-used
here.
The format can be TSQL-format in the resultset of a Stored Procedure without
changing the format in the database because this is used for the output to
the clients.

Same problem with dates !!!

thanks,

Filip



.

Relevant Pages

  • Re: Parameters
    ... ALTER FUNCTION dbo.My_Function2(@order nvarchar (10), ... In the listbox simple selection mode i used the following code: ... > generated sql and drop the stored procedure, or you can make the stored ... > It looks like your performing a query against a stored procedure. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: problems inserting into sql using SqlHelper.ExecuteScalar
    ... Here is the code and the stored procedure I'm ... > @Cont1FirstName nvarchar, ... > @Presort bit, ... > It returns the correct id number but the varchar fields are only recieving ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: STRANGE PROBLEM
    ... It is damn bad practise lah encik. ... im looping SqlCommand in a while loop. ... @MarkaLink NVARCHAR, ... You add parameters to the stored procedure in a loop.So u add the parameters while true.You should not add parameters in a loop ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Help Retrieving XML with HTTP within stored procedure ..
    ... > Within a stored procedure, I need to retrieve XML over HTTP from an ... you can make an nvarchar value. ... (Need nvarchar for OPENXML). ...
    (microsoft.public.sqlserver.programming)
  • Re: Datatype-convertion in TSQL
    ... Can't find a code for datetime-conversion from nvarchar format DD/MM/YYYY ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)