Converting data types

Having a slight problem. For whatever reason I had a column setup for nvchar but the only data i have in there is dates (MM/DD/YY) and a whole bunch of NULLs. I wanted to change the datatype to datetime but it gave me an error. I thought it was due to all the NULLs, so I removed all the nulls. It still doesn't work. I get the error : Conversion failed when converting datetime from character string.

I don't belive its because I have the data in mm/dd/yy format. I created another table and populated it with mm/dd/yy data as nvchar (50) and then converted to datetime and it worked fine.

any ideas?

Obviously, as you know, you get this error because the conversion could not convert some of the data (as it was not in the correct format). First of all, you can use IsDate function to see which data is giving you problem. Other than that, try this post. Just as a precaution, backup the table or database before you try anything.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1785290&SiteID=1

|||

Rather than trying to update the data type, try creating an additional field with a DateTime type. You can then copy all the dates that aren't null into this column and then drop the original column (and rename the new one if necessary).

|||

I would look through the data. When dates are stored as varchar, and especially if there isn't any validation when it is inserted into the database, you'll often see values like 01/01/3007 or 06/32/2007 or some other way it was entered that caused ambiguity with SQL Server. I've had the same thing happen in the past.

|||

Hi,

Review the Globalization configuration in the web.config, put en-US to work fine in the culture and uiculture attributes, by default is en-US, may be you change it.

greetings.

Converting data types

I've inherited a monster database that needs updating. It has a field called Birthdays that has been a text field and I want to convert it to datetime. The data entry people have been sloppy from time to time and some of the values generate errors when you try and change datatypes. Is there a way to change the datatype and have the oddball values left blank without bombing out the conversion process?

Thanksheh. how big is the database?

if it's not that big, then you can simply store them in a temp table, and move the values over one by one in a cursor style fashion. If it's absolutely enormous, I'd actually create another table just to store the data, and the same cursor fasion. Just make sure you put them in transactions so you can rollback accordingly based on any errors generated.

Since it's a one shot deal, you can even do it out of the database, and just manipulate all the values via code, then do the alter so it doesn't cause any problems.

Do you know specifically what the bad dates are? If so, via code, you can convert the dates based on how the errors were inputted, and update those records. :-\

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.)

Converting Data Types

I have a table with a field of Char(10) Data type

this field contains records of work time Attendance in a decimal format

Ex. I attend today for 8.30 this means eighthours and thirty mintutes

the main problem faced me to make some calculations on those records (sum, subtract, etc)

so I want to convert the data from char type to decimal or real using the next code but it doesn't work

select (cast (satreg, decimal) + cast (satot, decimal)) from timecard

can u please help me in the main issue how to sum char type records or at least how to convert them to decimal

Regards

create table test

(

time char(10)

)

insert into test (time) values ('8.30')

insert into test (time) values ('9.30')

insert into test (time) values ('10.30')

selectconvert(decimal(10,2), time )

fromtest|||

this can help:

it breaks your 8.30 to 8 and 30

SELECT substring(test,0, (patindex('%.%',test))),substring(test,(patindex('%.%',test)+1),len(test)) from test

Converting a type into another one

Hello,

I've got two tables. An old one an a new one. Called "tbl_Filme" and "tbl_Filme2". The differents are that the data types are a little bit smaller as in the old one.

So I've got a column wich should contains a datetime. Unfortunattley the datatype is just a normal date and not "smalldatetime" (the "tbl_Filme" was created with MS Access 2003). So I get an error message:

1> INSERT INTO tbl_Filme2 (Titel, Genre, Medium, Anzahl, Qualit?t, Filml?nge)
2> SELECT Titel, Genre, Medium, Anzahl, Qualit?t, Filml?nge
3> FROM tbl_Filme
4> go
Meldung '298', Ebene '16', Status '1', Server 'PREDATOR\SQLEXPRESS', Zeile 1
'The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.'

My goal is that I can copy the old table in the new table. If I solved that problem maybe you can help me with that problem, too:

I've programmed a programm for accessing the table in VB 2005. I've got theire a GridView where I can change the cells and a button to sync. the changed values with the SQL Database (2005). But finished with the update, the values aren't updated.

I thought this might be an error of my programming but first I tried to use Access for this (created a new Access Project *.adp). But there was an error message "Could update the RecordSet". Is this beacause of the SQL Server 2005 (maby I must wait untill an update, because Access 2003 is older then SQL 2005?)?

Thanks in advance.

OK, first problem. Rather than doing a automatic convertion between the old and the new one, I would check these incompabilities and solve them. Appearantly the datetime are two big / or too small to be reflected in a smalldatetime. So knowing that smalldatetime is defined in the scope of

from January 1, 1900, through June 6, 2079

You should use the query to identify these "old" ones and UPDATE them to a acceptable format for datetime (as above).

Then there should be no more problem with importing them. If you need the dates prior to 1900 or after 2079 you have to use the datetime data type.

Second problem: ""Could update the RecordSet"." This doens′t sound lkike a problem :-). Assuming that the error message is ""Could NOT update the RecordSet".", I would investigate the command that are passed to the Provider. Did you call the UPDATE method ? There has to be an inner exception which should explain the error message a bit more in detail. This would be more helpful to solve your problem.

HTH, jens Suessmeyer.

|||

Hello,

thank you for your answer. The reason why I wanted to choose the datetime is, that I want to write a time value. Is their any data type for only time without a date in it? But I will try your suggestion.

The second problem occured in Microsoft Access. Well, I startet a new project with a new connection to the SQL Server (with the user-ID "sa").

Then I saw all the tables, which were in the database. But I can only read, I cannot change anything. If I try to change a value the error "Couldn't update the RecordSet" occures.

|||

"Is their any data type for only time without a date in it"

-No.

" But I can only read, I cannot change anything"

Create a primary key in the access enviroment on the tables, that should help.

HTH, jens Suessmeyer.

|||

Unfortunattely their is a message (while opening the window where I can edit the data types of the columes) which sais that it is not possible to save the changes because the used SQL Server is newer than the Access version.

Edit:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=196509&SiteID=1

The problem is written their, too. No useable way to solve the problem. Maybe I will wait until the Office 2007.

I have got one question contains the "SQL Server Management Studio". Where do I get this GUI for the SQL 2005. I don't find it.

Edit:

Sorry, found it. :-)

Edit:

Something is strage. After I "played" a little bit with the SQL Server Management Studio" I tried it again with the Microsoft Office Access 2003 and now it works!

Converting 1000 into 10.00

I have been given a Product table whoes all column types are varchar(8000)

One of the column is Price and other is DecimalPosition. Price column includes price without any decimal place and the data in DecimlaPosition column determins where the decimal should be placed.

So for instance, if the Price column includes '1000' and DecimalPosision includes '2' >> then it means that the actual price for this product is '10.00' and NOT '1000'. Similarly, if the DecimalPosision includes '3' >> then it means that the actual price for this product is '1.000' and NOT '1000'
My question is that when I am getting the price for a product from this table, how can I get the price in the correct format, e..g like '10.00' and not '1000'
Should I use SQL statements to convert 1000 into 10.00 or should I use some sort of programming logic to convert 1000 into 10.00.
kind regards

select substring(price,1,decimalPosition) + '.' + substring(price, 1 + decimalPosition, len(price) - decimalPosition)
from YOURTABLE

Nick

Edit: include decimal|||

There's a slight problem in Nick's code. It should be:

select substring(price, 1, len(price) - decimalPosition) + '.' + substring(price, len(price) - decimalPosition + 1, decimalPosition) as col1

However, no matter how you argue, that is just some flawed design. What I would strongly recommend is set up a migration "roll-out" plan for the data that is already in production and convert them to a standard price structure where you drop that decimalPlace column and correct the Price column into an real number.

Cheers,

Justin

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?
What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER COLUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegro ups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER COLUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER CO
LUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/

convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/

sqlsql

Convert decimals and money to fixed length varchar format.

I am trying to convert decimals and money data types to a universal format and convert to varchar. The format is:

+00.000000

The format must be exactly this format.
The sign must always be present.
There must always be two numbers to the left of the decimal.
There must always be six to the right.
No matter the value, round to six decimals.

Examples:

1.123 : +01.123000
-4 : -04.000000
.98672385 : +00.986724

I have tried every combo of Convert, Cast, Right, etc and every time it doesn't seem to work.

Any ideas?

It is recommended to do the formating in the presentation layer. Try:

select

case when sign(c1) < 0 then '-' else '+' end +

right('00' + str(abs(c1), 8, 6), 9)

from

(

select 1.123 as c1

union all

select -4

union all

select .98672385

) as t

AMB

|||Try this

Code Snippet

declare @.num decimal(18,6)

SET @.num = .98672385

SELECT FmtNum = CASE WHEN @.num < 0 THEN '-' ELSE '+' END +
RIGHT('00'+CONVERT(varchar(9),CONVERT(decimal(9,6),ABS(@.num))),9)

|||You rock.

Thanks. It works great.