Datatype-convertion in TSQL

[Filips Benoit]

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

[Erland Sommarskog]

Filips Benoit (be***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Filips Benoit]

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" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Filips Benoit (be***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[labrys]

I think the 5th one at the bottom of this post might help.
But I usually find it easier to convert date and stuff at the software end? .. I don't care what format the date is when using asp.. when i do db updates i enter it in as YYYY-MM-DD [ year(now())&"-"&month(now())&"-"&day(now()) ] (the db will then convert it itself to whatever it uses) and when reading back out i convert it again to the format i want [ day( rs.Fields.Item("theDATE").Value )&"/"&month( rs.Fields.Item("theDATE").Value )&"/"&year( rs.Fields.Item("theDATE").Value ) ]

or you could look up the following command in google to find the right end number for the conversion for you

CONVERT(DATETIME,'02-28-04', 110)
The 110 denotes the format as mm-dd-yy

or look at this site http://www.databasejournal.com/featu...le.php/2197931



CONVERT ( data_type [ ( length) ] , expression [ , style ] )

PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +
CONVERT(CHAR(19),GETDATE())
PRINT '2) HERE IS MM-DD-YY FORMAT ==>' +
CONVERT(CHAR(8),GETDATE(),10)
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' +
CONVERT(CHAR(10),GETDATE(),110)
PRINT '4) HERE IS DD MON YYYY FORMAT ==>' +
CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>' +
CONVERT(CHAR(9),GETDATE(),6)
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' +
CONVERT(CHAR(24),GETDATE(),113)

Here is the output from the above script:

1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>Feb 5 2003 5:54AM
2) HERE IS MM-DD-YY FORMAT ==>02-05-03
3) HERE IS MM-DD-YYYY FORMAT ==>02-05-2003
4) HERE IS DD MON YYYY FORMAT ==>05 Feb 2003
5) HERE IS DD MON YY FORMAT ==>05 Feb 03
6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>05 Feb 2003 05:54:39:567

[Madhivanan]

>>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" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Filips Benoit (be***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Filips Benoit (be***********@pandora.be) writes:

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
It's there on that page. You need to read the table headers a little more
closely.
What is the max lenght of sql_variant?
8000 bytes.
Now CPRP_VALUE = nvarchar 1024 Is it possible to change the
field-datatype having existing data?

One way or another it is. I would guess that ALTER TABLE ALTER COLUMN works,
but I have not actually tried it. But obviously if you do that, all
data will be converted with a basetype of nvarchar, so you would have to
write some script to repopulate the column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Filips Benoit]

Thank for following me,

I'm still not done with that date-convertion-problem

I want to be able to run the SP below but it triggers error = 'Aritmitic
overflow error converting expression to data type datetime'

What am i missing.

Thanks²,

Filip

Alter Procedure StoredProcedure1

-- dates in CPROP_VALUE ( nvarchar) as '1/1/2000' or'01/01/200' or
'1/06/2000' tested in access with function IsDate()

As

SELECT CPROP_PRP_ID,

CPROP_VALUE
FROM dbo.COMPANY_PROPERTY

where CPROP_PRP_ID = 370 and

convert(datetime, CPROP_VALUE, 103 ) = getdate()

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Filips Benoit (be***********@pandora.be) writes:

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
It's there on that page. You need to read the table headers a little more
closely.

What is the max lenght of sql_variant?

8000 bytes.

Now CPRP_VALUE = nvarchar 1024 Is it possible to change the
field-datatype having existing data?

One way or another it is. I would guess that ALTER TABLE ALTER COLUMN

works, but I have not actually tried it. But obviously if you do that, all
data will be converted with a basetype of nvarchar, so you would have to
write some script to repopulate the column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Filips Benoit (be***********@pandora.be) writes:

-- dates in CPROP_VALUE ( nvarchar) as '1/1/2000' or'01/01/200' or
'1/06/2000' tested in access with function IsDate()

As

SELECT CPROP_PRP_ID,
CPROP_VALUE
FROM dbo.COMPANY_PROPERTY
where CPROP_PRP_ID = 370 and
convert(datetime, CPROP_VALUE, 103 ) = getdate()

As I understood it, CPROP_VALUE can include all sorts of values, including
stringified floats. Not all these values will convert to datetime,
and will give you an error of some sort.

Yes, I can guess that if CPROP_PRP_ID = 370, then all values are good
date strings, but there is no operator shortcutting in T-SQL. The
only way to avoid undesired conversion attempts is to use CASE:

WHERE CASE CPROP_PRP_ID
WHEN 370 THEN convert(datetime, CPROP_VALUE, 103 )
END = getdate()
AND CPROP_PRP_ID = 370

The last condition is logically redudant, but if you have an index on
CPROP_PRP_ID, it's a good thing to add it.

Note the above query is not extremely meaningful. getdate() returns
the current date and time with a precisions on 3.33 ms. The odds that
the SELECT will return any rows appear slim.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx