Converting Data Types

I need to be able to convert a numeric data type to a text data type.

I've tried the TO_CHAR and the DBCONVERT functions but I am returned an error stating that they are not recognized functions.

I'm using the Query Analyzer on a Windows 2000 SQL Server.

Any help would be appreciated.

TechRickYou can use the cast or convert functions.|||Thanks,

I was just reading up on the CAST function. However, I've not been able to make it work yet.

In correction to my original post, I need to take a MONEY data type and convert it to a TEXT data type. I hope this possible.

Thanks again,

TechRick|||Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles|||You can also do it this way:

select cast(cast(price as varchar(30)) as text) from titles

But since the conversion is implicit between varchar and text you don't need to do this.|||Thanks again for the help. Your suggestion works great but when I try to update the data I get this:

Server: Msg 260, Level 16, State 1, Line 31
Disallowed implicit conversion from data type varchar to data type money, table..., column 'PRICE'. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 31
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

I'll let you know once I get it worked out.

Best Regards,
TechRick|||The way the error is reading is that you are trying to put a varchar into a money column - is that correct ?|||Yes, I have a large list of items collected from various tables and there are prices associated with these items. A good majority of these items have no price (.0000) and I would like to exchange the .0000 price for a 'CALL' or something similar.

I could not update the records with a "text" substitute so I thought I would convert the data type to TEXT so I can plug in whatever I want.

Thanks again,
TechRick|||Why did you choose text over varchar ? Could you post your update statement as well as the definition of the table you are updating ? You have to explicitly convert varchar to money and vice-versa using either cast or convert. But I am a little confused about the first error you received - why are you trying to put a varchar into a money column ?|||You'll have to excuse me, I've only been working with SQL for less than 3 weeks. I've been learning as I go (with my Dummies and Sam's books and this forum). I've already written a few complex queries and as for this particular situation, this is the last obsticle I need to overcome to put this query to rest.

Based on the error I was getting I assumed that I was doing something wrong but I didn't have the time to completely research it. I'll pick it back up on Monday. I think I need to understand the convert and cast functions better before I can make use of them. I'll be working towards that end.

As for now, I'm away from work and don't have easy access to the code. I'll post it Monday after I tinker a little more.

Thanks for the help.

TechRick|||Ah, by "convert" you mean that you would like to change the data type of a column from money to varchar so that you can store a mix of data in a single column. The easy answer is to just change the data type of the column in Enterprise Manager. If SQL Server can find a reasonable way to preserve the data, it will. Thereafter you can store any character data in the column, e.g. "Call" or "Operators are standing by!".

The "correct" answer is rather different, and a valid subject for debate. If you do not have a price for an item, the correct representation in the database should be different from a free item. For example, you might use the value NULL to indicate "call for price" and $0.00 for a free item. Alternatively, it may make more sense in your application to have a separate means of flagging items for which you don't want to publish a price, qualify for free shipping, have quantity discounts, ... . More columns or tables may be needed.|||Agreed about the 'correct' answer. Fortunately and unfortunately, I didn't write the application so I'm working with what's there.

I managed to get it taken care of by adding the convert on my select statment.

SELECT UPPER(ITEM), DESCRIPT, Q_STK, QTY_RESERVE, CAST (SELLPRICE AS VARCHAR(15)),...

By doing it this way I am able to manipulate the data any way I want.
Add 'CALL', 'FREE', etc.

Thanks all for the help. One last item and this query is behind me.

Best Regards,
TechRick|||Originally posted by rnealejr
Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles

rnealejr, I noticed as I was going back through all these posts that you were right on the money a long time ago! Thanks for the help. I think I was trying to perform an update on the column using the cast rather than taking the data in converted from the start. Anyhow, just wanted to thank you for your help. Too bad I had to learn the hard way.

Best Regards,
TechRick|||Thanks for the email and compliment as well as a good pun (right on the money) - I enjoyed that.

Good luck.|||Another handy tool for fudging return values within a query is throwing in a CASE, e.g.:

select Description, ServingSize, case when Price<>0.0 then Convert(VarChar,Price) else 'Call' end as 'AdvertisedPrice'
from PiecesParts where Fused=1

Note that there are two slightly different versions of CASE. One lets you test a single expression against multiple values, while the other lets you test multiple expressions.

You can swindle a lot of logic into a CASE or nested CASEs. For example, it could check for quantity price breaks or apply discounts based on data from other tables or variables. The result is just another (computed) column in the recordset returned from the query. (As such, it isn't writable.)

convert to T-SQL

Can anybody convert to T-SQL;
select distinct to_char(sysdate,'FMDAY, MONTH DD, YYYY HH:FMMIAM') TODAY
, d_aaa_insthdg CNTRHD
, decode(trunc((d_vst_btcdte - nvl(n_per_birth,d_vst_btcdte))/10000,0),0,'no DOB',
14,'14-16',15,'14-16',16,'14-16',
17,'17-20',18,'17-20',19,'17-20',20,'17-20',
21,'21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25',
26,'26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30',
31,'31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35',
36,'36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40',
41,'41-45',42,'41-45',43,'41-45',44,'41-45',45,'41-45',
46,'46-50',47,'46-50',48,'46-50',49,'46-50',50,'46-50',
51,'51-55',52,'51-55',53,'51-55',54,'51-55',55,'51-55',
56,'56-60',57,'56-60',58,'56-60',59,'56-60',60,'56-60',
61,'61-65',62,'61-65',63,'61-65',64,'61-65',65,'61-65',
66,'66-70',67,'66-70',68,'66-70',69,'66-70',70,'66-70',
'71+') AGE
, sum(decode(d_vst_dontyp,'WB',1,0)) DRAW1
, sum(decode(d_vst_dontyp,'xx',1,1)) TOTAL0Didn't I already do this for you?

Convert to Julian Date

In Oracle, we can write query to convert current date to Julian date
SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
This returns the value 2453754
I think there is no function in SQL Server to convert to Julian date
How do I use the same logic in SQL Server?
MadhivananMadhivanan wrote:
> In Oracle, we can write query to convert current date to Julian date
> SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
> This returns the value 2453754
> I think there is no function in SQL Server to convert to Julian date
> How do I use the same logic in SQL Server?
> Madhivanan
Try this:
DECLARE @.dt DATETIME ;
SET @.dt = CURRENT_TIMESTAMP ;
SELECT
DATEDIFF(DAY,'17530101',@.dt) +2361330.5
+DATEDIFF(SECOND,DATEADD(DAY,
DATEDIFF(DAY,'17530101',@.dt),'17530101')
,@.dt)/86400.00
AS julian_date ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CORRECTION. Use SET @.dt = GETUTCDATE() instead of CURRENT_TIMESTAMP.
Julian dates are always expressed relative to UTC and not local time.
If you supply your own date and time you'll have to convert it to UTC
yourself.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
DECLARE @.JulianDate AS INT
select @.JulianDate = 2415021 + datediff(dd, convert(datetime,
'1900-01-01',103),getdate())
PRINT @.JulianDate
This will give the julian date and to get back the current data,
select dateadd(dd, @.JulianDate-2415021, convert(datetime, '1900-01-01',103
))
Thanks
Sree
"Madhivanan" wrote:

> In Oracle, we can write query to convert current date to Julian date
> SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
> This returns the value 2453754
> I think there is no function in SQL Server to convert to Julian date
> How do I use the same logic in SQL Server?
> Madhivanan
>|||Thans David,
You used the value 17530101. Is this the number of days between January
1, 4713 BC and January 1, 1900?
Sree,
What does the value 2415021 refer to?
Madhivanan|||In SQL Server the datatype DateTime can store dates in the range - January
1, 1753 through December 31, 9999
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1137583289.974927.92200@.g44g2000cwa.googlegroups.com...
> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
>|||Madhivanan wrote:
> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
'17530101' just represents January 1, 1753, which is just the earliest
date supported by SQL Server. I used that as a base date for
calculating the Julian date. 2361330.5 is the Julian date for midnight
on 1753-01-01.
In Sree's case 2415021 is the Julian date for 12 noon on 1900-01-01.
Sree's calculation ignores the time portion of the current time and
returns the Julian day number for 12 noon on that day.
As I explained in my correction, Julian dates refer to UTC (GMT) time
so it's not really correct to use GETDATE() or CURRENT_TIMESTAMP as the
input to this calculation. You can use GETUTCDATE instead. In practice
if you are only interested in the integer day number then whether you
choose to base it on local time or UTC may depend on your requirements
and on how the value is to be used.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Its the Julian Date equivalent to "1900-01-01 00:00:00.000".
Julian date is the number of days that have elapsed since "1900-01-01
00:00:00.000". So the logic here used is simple, add Julian equivalent of
"1900-01-01 00:00:00.000" ie "2415021" to number of days from "1900-01-01
00:00:00.000" to current data(getdate()).
Hope you got the idea.
Thanks,
Sree
"Madhivanan" wrote:

> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
>|||Thanks David and Sree
How did you calculate the value 2361330.5 for the date 1753-01-01?
Madhivanan|||Madhivanan wrote:
> Thanks David and Sree
> How did you calculate the value 2361330.5 for the date 1753-01-01?
> Madhivanan
http://aa.usno.navy.mil/data/docs/JulianDate.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--