Convert T/F Char To Bit DataType
Jun 4, 2008
Hi all,
There are several columns called enabled with a char datatype in my database. One enabled column per table. These columns either have a value of T or F (true or false), depending on whether they're enabled or not. I want to change these columns to a bit datatype and insert the relevant value of true or false...
I guess the best way to do this is to add a new column to a table with a bit datatype, and based on the value in the current enabled column, insert TRUE or FALSE.
Anyone ideas on the best way to accomplish this?
Thanks.
View 4 Replies
ADVERTISEMENT
Sep 17, 2003
Database is SQL Server 2000
I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.
What is the syntax to convert char(6) to datetime?
Thank you in advance.
View 1 Replies
View Related
Dec 31, 2003
Hi,
I read the topic from JROdden and this case is similiar but...
I got several varchar fields with
values like
1.2
1.3
... these I can covert with
select CONVERT(dec(5,2), fieldname) as fieldname
In fact I also solved undefined- and NULL-values with.
CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR
GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,
But now there are values like
1,4 and these ones neither CONVERT nor CAST will handle.
I tried the
SELECT DISTINCT KMPAUSCHALE
FROM extr_INTFIRMA
WHERE (isnumeric(KMPAUSCHALE) = 1)
and get
0,40
0.25
0.30 and so on...
The error is:
[Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))
I think the easiest way would be to insist on higher data quality but
I also would like to solve this interesting challenge.
Thanks for any hints
By the way, I followed rudys link to
http://rudy.ca/afdb.html
and now I know how I could protect myself !!!!
There must be a voice in my head saying:
Try the db-forum, try it and stay happy... ;-)
best regards and have fun with new year eve.
Michael
View 8 Replies
View Related
Oct 25, 2000
here is my problem:
i have a variable @sid_x as binary(16) = 0x4CF254AB0BA5D411AA3E00508BC5C413
and i want to use it as argument in sp_addlogin statement.
select @sqlcmd = 'sp_addlogin "test", @sid = ' + @sid_t
/* this doesn't work, because @sid_t is binary... */
select @sqlcmd = 'sp_addlogin "test", @sid = ' + convert (char (20), @sid_x)
/* this doesn't work either, because it doesn't convert to binary text */
my question, is there any way i can get @sid_x in follow text format
0x4CF254AB0BA5D411AA3E00508BC5C413 ?
Thanks a lot!
View 1 Replies
View Related
Jul 12, 2007
I'm designing a database that will not be that large (I would be surprised if it ever surpassed 50,000 rows across all tables), but will be accessed quite often, so I am doing my best to optimize its structure, such as doing 3NF, selecting appropriate data types, etc.
I have a few columns that will contain numeric data (such as an invoice number (from an external source) or location ID). However, one of my classes in college was about database design, and we were taught that if you won't be doing mathmatic computation on a field (such as the invoice or location fields I mentioned earlier), then you should use a string literal type (char, varchar, etc.)
Unfortunatly, the professor did not explain much as to why this should be done. From the standpoint of semantic analysis, these types of fields are probably more labels than they are numbers. However, I don't find that very convincing (or even helpful).
In short, my question is that given a column holding numeric data that isn't worked on in a mathematical sense, is it really better to mark it as a string literal than a number? Any articles or studies I can read relating to this?
I would think that comparisons would be faster with int, as well as data storage (though, as mentioned, that's not as big of a concern). Sadly, Google doesn't have many helpful resources. (Lots of stuff on char vs varchar, though.)
View 12 Replies
View Related
Jul 20, 2005
I am trying to run the following query:ALTER TABLE dnb_profileALTER COLUMN [family update date] datetimeand I keep getting the following error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.The statement has been terminated.Can anyone tell me how I can do this successfully??Thanks,Connie SawyerFoley & LardnerJoin Bytes!
View 2 Replies
View Related
Jan 9, 2006
Hi All:
I am new to Sql 2000 database,Now I'm planing to create a table in my databse,my table included below fields like this :
PoNo(the length is 15 characters) ,Supplier Name(the length is 50 characters).etc
but I don't how to select the datatype for them. should I select Char or VarChar ?
which one is the best slection ?
thans in advanced!
View 5 Replies
View Related
May 29, 2015
How can I get time difference of the following record :
STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM
I have tried it by below query,
SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER
but it gives following error message
[color=red]Operand data type time is invalid for subtract operator.[/color]
View 6 Replies
View Related
Feb 12, 2008
I'm looking through some data in a third party application, trying to figure something out for a report. There is a char(1) column holding control characters that apparently effects the status of certain records, and whether they should appear on the report. I want to look at the integer value of those characters rather than the character representation, but I can't get it to work. I keep getting "Error converting data type varchar to numeric." I'm trying to do something like this:
Code:
SELECT CONVERT(int, MyColumn) As IntValue FROM MyTable
I've tried every combination of cast and convert I can think of, as well as numeric data types other than int. There aren't that many distinct values, so I'm just going to do this manually, but I still want to know why it won't work, or what I'm doing wrong. Any thoughts?
View 2 Replies
View Related
Jun 12, 2007
I have a Column called PostNr which is of type Char(4)
I want to be able to Convert it to int
How can I accomplish that in query analyser, I tried changing it in Ent Manager, it keeps timing out.
I data in Post is in this format, does not contain illegal character just number
View 5 Replies
View Related
Sep 29, 2007
Hi all,I have a column LateHours (Varchar). I want to put it in another table which has Latehours column in Decimal(4,2) Example = +04:33 Should be 4.33 (Only + values) Char to Decimal (4,2) Please Help me,Thanks,Janaka
View 3 Replies
View Related
May 31, 2002
I am trying to convert Varbinary to Char it keeps returning this ...
--------------------------------------------------
´å
(1 row(s) affected)
Here is the code that I am using....
declare @name varbinary(50)
set @name =0x12b4e5
SELECT Convert(char(50),@name)
Can someone please help! ~Dana
View 3 Replies
View Related
Mar 28, 2004
Hi,
I have DB2 date value 00000000. If I'm exporting to SQL server using openquery that is automaticaly converting to char of 8 and stored as the same value 00000000.
My question is how I can convert them as datetime value in SQL server 2000.?
View 1 Replies
View Related
Jul 3, 2001
Have a requirement to:
Convert char(7) YYYY-MM variable (eg. '2001-07') to both:
datetime '2001-07-01 00:00:00.000' and
datetime '2001-07-01 23:59:59.997'
thx in advance!
View 2 Replies
View Related
May 23, 2008
hi, how can I convert a float to a char so I can do:
'£' + convert(char,amount) AS [money]
When I do the above the number is like 1.07848e+006
rather than 1078480
thans for any help.
jamie
View 20 Replies
View Related
May 26, 2008
if i use the query
SELECT iditem, CONVERT(char(20), dt, 105) FROM planeamento
it works just fine.. but in this case the dt (date) field is not recognised..
SELECT planeamento.iditem, idmodelo, item_planeamento.idproduto, item_planeamento.idpele, cor, ordemfabrico, qtd, CONVERT(char(20), dt, 105) FROM produto INNER JOIN item_planeamento ON produto.idproduto = item_planeamento.idproduto INNER JOIN planeamento ON item_planeamento.iditem = planeamento.iditem WHERE planeamento.idfabrica = 51 AND alterado = 'FALSE' AND estado = NULL GROUP BY planeamento.iditem, produto.idModelo, item_planeamento.idProduto, item_planeamento.idPele, item_planeamento.cor, item_planeamento.ordemFabrico, item_planeamento.qtd, planeamento.dt
before, id just had "id" where "CONVERT(char(20), dt, 105)" is, but the date was printed in a unwanted format..
can you help me?
thanks a lot!
View 5 Replies
View Related
Dec 26, 2014
declare @new table(
id int not null identity(1,1),
dat char(10))
insert into @new
select'111214'
union
select'121214'
select cast(dat as datetime)from @new
-----------------------
2011-12-14 00:00:00.000
2012-12-14 00:00:00.000
but me need results it
2014-12-11
2014-12-12
View 2 Replies
View Related
Aug 26, 2005
Select Cast('100.1234' as float)give me the result 100.1234Now when I convert it back to char I want exactly 100.1234Select Convert(char(100),Cast('100.1234' as float))Gives me 100.123 (Here I was expecting 100.1234)When I doSelect STR(Cast('100.1234' as float),25,4)I get back the result as 100.1234However here I am not sure how many digits do I have after the decimalpoint. If I put some value likeSelect STR(Cast('100.1234' as float),25,8)I get 0's appended to it, which is again not desired.Thanks in advance,Jai
View 4 Replies
View Related
Feb 22, 2008
We have the same database in three different environment. The statement below works just fine in two database environment, but I am getting the error in the third one. All the databases are Microsoft SQL Server 2005. I could not pine point the source of the issue. Please any input that you might be able to provide. Thanks
The issue in simplest from
DECLARE @UDF_Value nvarchar(255)
set @UDF_Value = 111
SELECT CONVERT(money, @UDF_Value)
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
View 7 Replies
View Related
Sep 27, 2007
I've been trying to solve this problem or just find an alternative to do this. I have filled a temp table with certain values for all columns except those columns that rely on calculations that need certain cell values from other columns in the temp table. I have a while statement that runs through each cell for one calculation column and this is the code that updates the fields on the calc column:
Code Block
EXEC('UPDATE #tblTemp
SET #tblTemp.['+@CalcColumns+'] = (CAST(ISNULL((SELECT #tblTemp.['+@Column1+']
FROM #tblTemp
WHERE #tblTemp.PK = '''+@PubKey+'''), 0.00)as money) - CAST(ISNULL((SELECT #tblTemp.['+@Column2+']
FROM #tblTemp
WHERE #tblTemp.PK = '''+@PubKey+''') , 0.00)as money))
WHERE #tblTemp.PK = '''+@PubKey+'''')
The problem is that it will not let me convert the two values to money. The error is:
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
This is necessary to calculate the difference between these two cells from the temp table. I would appreciate any help! Thanks!
View 6 Replies
View Related
Mar 26, 2004
Hi,
I am extracting data from DB2 into SQL server using DTS. We have the data type char(5) in DB2 and int in SQL server.
I tried to give the
select integer(columname) as columname from tablename in data source.
I did parse the query it was Ok but If I tried to execute that giving the following error.
Can you give me your answere for this issue.
---------------------------
Package Error
---------------------------
Error Source : Microsoft OLE DB Provider for ODBC Drivers
Error Description : [StarQuest][StarSQL ODBC Driver][DB2]THE VALUE OF A CHARACTER STRING ARGUMENT WAS NOT ACCEPTABLE TO THE INTEGER FUNCTION
---------------------------
OK
---------------------------
View 4 Replies
View Related
Jan 2, 2002
Using SQL 2000 I have data in a sql table that is store in varchar like below
5.00
15.00
9.00
The integer part will never be bigger than 20. I need to move it to another SQL table that is char(5). I need the results that go in that table to like like below
05.00
15.00
09.00
I looked at the replace and cast but couldn't get the results. Any better approaches?
Thanks
View 3 Replies
View Related
Aug 7, 2001
What is the easiest way to convert '3/21/1959' to '19590321' I looked at
the CAST and convert and can't come up with a solution.
Thanks
View 3 Replies
View Related
Jan 4, 2006
Hello,
Is there a way to convert varchar or char to float?
Thank you
View 6 Replies
View Related
Sep 22, 2014
I have to store the result of a calculation in a column of type CHAR(7) (and am unable to change the column type).
The calculation can have results ranging in size from 0.1234567 to 99999999.
In the first case, I would need to store the value of 0.12345 in the column. In the later case, an error should be thrown.
So I need to store all of the significant digits from the left of the decimal (if there are < 7) and as many of the digits to the right as will fit into a CHAR(7), with the remaining precision being truncated.
View 6 Replies
View Related
Sep 29, 2005
I have imported some data to sql2k from my old system. Somehow, it importedinvoice amount to char type.I just created another column called invamt2 type NUMERIC so I can copy orconvert content of invamt which is type CHAR. There are about 50,000records.How can I convert/cast from char type to numeric type ?Thanks
View 2 Replies
View Related
Aug 23, 2007
I can't seem to find a method of converting a CHAR(8) column in the form ofYYYYMMDD to an actual date such as mm/dd/yyyyCan anyone point me in the right direction?Thanks
View 17 Replies
View Related
Dec 17, 2007
I'm trying to convert a char(24) column to the datetime format. This is my query:
select CONVERT(datetime, [Date], 121) from Table
The date in the char(24) column has this format: 2007-12-14 14:45:31.735
When executing this statemant it says it cannot convert this char to datetime. But when I execute this statement, it works:
select CONVERT(datetime, '2007-12-14 14:45:31.735', 121) from Table
What am I doing wrong?
View 7 Replies
View Related
Feb 15, 2008
Hi,
i want to perform the following conversion to yyyy-mm-dd hh:mm: ss SMALLDATETIME format :
print convert( smalldatetime,'2008-02-14 10:16:00', 120)
But i get the error and the wrong conversion format:
Feb 14 2008 10:16AM
Msg 296, Level 16, State 3, Procedure TSU_CalcTempoRespostaPercInterv, Line 43
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Any way to achieve this ?! i'm tearing my hear off with this issue #"#"#$"#"$#$"
View 9 Replies
View Related
Feb 5, 2008
Hi ,
I am trying to convert a char field of lenght 5 into numeric (5,2) 999.99 format ,
i am getting an arithmentic overflow error.
The Character Data is 5 in length
It works fine if the Data is 00000
it fails if there is any number in the data
Like 10000
or 05000
How do i represent these into Numeric (5,2)
please let me know what taransformation to use and how.
Thanks,
View 2 Replies
View Related
May 18, 2007
The problem...
A new value is inserted automatically with (convert(smalldatetime,getdate()103)) to a smalldatetime field (date) into a new row in the db
Existing data needs to be updated via an html form in the format ”dd/mm/yyyy” - I get this when I try: “Error The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value”
Obviously the db field doesn’t like trying to update a smalldatetime field with a char type value. As the date field needs to use some SQL later with DATEADD, I can’t change the date field to char. (The date feild accepts ”dd/mm/yyyy” if I use Enterprise Manager and type it strait in but not via the update on a web page?)
The question...
How do I change the update SQL to convert from Char to Smalltimedate to accept dd/mm/yyyy?
( I am using MS-SQL and ASP3 JS and new(ish) to MS-SQL)
View 8 Replies
View Related
May 6, 2011
How do I convert (n)(var)char to time? I tried several ways including a simple:
SELECT CONVERT(time,'143825',108)But it always gives me this error:
Conversion failed when converting date and/or time from character string.
View 9 Replies
View Related
Nov 6, 2007
I am trying to write a Procedure in SQL 2005 that retreives a list of rows from the database. I pass to the procedure sorting parameters. This procedure works fine for all of the fields that I enter in the Order By, except for one.
The field that does not work is a varchar(500) field (named Description).
I am not sure what the problem is.
The error that I am getting is:
Msg 235, Level 16, State 0, Line 13
Cannot convert a char value to money. The char value has incorrect syntax.
Attached is the T-SQL Code. The declare and set statements in the beginning are for informational purposes.
Any help is appreciated.
------------------------------------------------------------------------------------------------------
declare @Match varchar(75)
declare @NumRows int
declare @StartPos int
declare @OrderBy varchar(25)
declare @OrderDir varchar(4)
set @Match = 'dog% AND bark%'
set @NumRows = 25
set @StartPos = 100
set @OrderBy = 'Description'
set @OrderDir = 'ASC'
WITH catitems AS
(
SELECT ci.ID, ci.SupplierCode, ci.Description, ci.AUDIO_LINK, ci.SoundLength, ci.HighCost, ci.Channels,
ROW_NUMBER() OVER (ORDER BY
CASE
WHEN @OrderBy='ID' AND @OrderDir='ASC'
THEN CAST(ci.ID AS int)
WHEN @OrderBy='SupplierCode' AND @OrderDir='ASC'
THEN CAST(ci.SupplierCode As int)
WHEN @OrderBy='Description' AND @OrderDir='ASC'
THEN CAST(ci.[Description] As varchar(500))
WHEN @OrderBy='HighCost' AND @OrderDir='ASC'
THEN CAST(ci.HighCost AS money)
WHEN @OrderBy='Channels' AND @OrderDir='ASC'
THEN CAST(ci.Channels AS smallint)
END ASC,
CASE
WHEN @OrderBy='ID' AND @OrderDir='DESC'
THEN CAST(ci.ID AS int)
WHEN @OrderBy='SupplierCode' AND @OrderDir='DESC'
THEN CAST(ci.SupplierCode As int)
WHEN @OrderBy='Description' AND @OrderDir='DESC'
THEN CAST(ci.[Description] As varchar(500))
WHEN @OrderBy='HighCost' AND @OrderDir='DESC'
THEN CAST(ci.HighCost AS money)
WHEN @OrderBy='Channels' AND @OrderDir='DESC'
THEN CAST(ci.Channels AS smallint)
END DESC) As RowNo
FROM TableName As ci
WHERE CONTAINS (ci.Keywords, @Match) AND ShowOnWeb=1
)
SELECT catitems.ID,
catitems.SupplierCode,
catitems.Description,
catitems.AUDIO_LINK,
catitems.HighCost,
catitems.Channels
FROM catitems
WHERE RowNo BETWEEN @StartPos AND @StartPos + @NumRows -1
------------------------------------------------------------------------------------------------------
Thanks,
Aric
View 1 Replies
View Related
