SQL Server 2012 :: How To Convert Varchar Value To INT

Apr 25, 2014

I am new to T-SQL development and here's what I am trying to do.

declare @cmd varchar(255)
set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'

while (@cmd <=13)
begin
insert into #dbcheck values (@cmd)
Set @cmd = @cmd + 1
end

but it is giving me an error

'Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.'

Note - #dbcheck has only one column id int

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Convert Varchar To Time

Mar 9, 2015

I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.

CPUTM = 335:55:20.97 duration time
CPUZIPTM = 0:00:01.96 duration time

select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')

View 2 Replies View Related

SQL Server 2012 :: Table With Column Data - Convert Varchar To Int

Jul 8, 2015

I have a table with column "Data" as VARCHAR, with entries like below.

1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68

I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.

SELECT CAST(dataAS INT) FROM record_tab

getting below error

Conversion failed when converting the varchar value 'A1'

View 9 Replies View Related

Select Convert(varchar(16), Getdate(), 101)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4)

Sep 26, 2007



select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

From above query I get

mmddyyyyhhmm

but it' s yyyy and hour can not be separated

04/12/200702:05

How can I separated the year and hour ?

Thanks
Daniel

View 2 Replies View Related

SQL Server 2012 :: Storage Of VARCHAR (MAX) When Null

May 7, 2014

If I have a table

CREATE TABLE [dbo].[logg](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](MAX) NULL)

insert logg (Details) values('')
insert logg (Details) values(null)

Will both statements above access only a single page (as it fits into one page) or does the VARCHAR(MAX) always put its data on a separate page. If so, is the null insert treated differently from the '' insert?

View 2 Replies View Related

SQL Server 2012 :: Conversion Failure Varchar To Int

Dec 15, 2014

How do I pass a variable to an INT calculation?

CREATE PROC CLEAR_MY_TABLE
@TableStat varchar(30)
AS
If OBJECT_ID('MyDB.dbo.' + @TableStat + '') is not null
BEGIN
---PRINT 'I FOUND THE TABLE'
DECLARE @count INT = -1;
SELECT @count = (Select COUNT(*) FROM [dbo]. + @TableStat);
IF (@count > 0)
BEGIN
TRUNCATE TABLE @TABLESTAT
END

View 8 Replies View Related

SQL Server 2012 :: Converting Varchar To Datetime?

Mar 19, 2015

I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.

View 9 Replies View Related

SQL Server 2012 :: SET 1 Symbol Instead Whole String In Varchar Value

Jun 12, 2015

I have such Function:

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'TwoDigitsNumber' AND type = 'FN')
DROP FUNCTION MinimumOFThree;
GO
CREATE FUNCTION TwoDigitsNumber(@a int)
RETURNS nvarchar(20)

[Code] ....

The only first letter 'f', 's', 'e' is inserted in value instead 'first', 'second', 'equal'.

Why ? How can i insert whole string

View 9 Replies View Related

SQL Server 2012 :: Varchar To Datetime Conversion

Aug 14, 2015

I have column moddat which is of varchar(10,null)

Here is my data:
20020415
20020508
19991104
19990701
20040514
20021112
20020124
19990628
20020514
20010822

I want those data in this format YYYY-MM-DD

How to convert varchar to datetime?

View 2 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Varchar Value

Dec 19, 2013

Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:

CREATE TABLE [dbo].[tblKppHierarchyPcl]
(
[ID] NUMERIC(18,0) NOT NULL,
[Name] VARCHAR(500),
[PartStructureKey] NUMERIC(18,0) NOT NULL,
[PartNumber] VARCHAR(500) NOT NULL,
[ParentPartNumber] VARCHAR(500) NULL,

[code]...

Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.

View 8 Replies View Related

SQL Server 2012 :: How To Get Email Address From Varchar Column

Mar 11, 2014

i have a situation where is need to get email address from a varchar column. Here is some sample data for five records; The format can be change.

dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk

Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;

dummy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;

dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;

dummy text;
t: +11 (0) 222 222 2222;
e: myemail@test.com;
w:http://www.yahoo.com/faqs;

View 4 Replies View Related

SQL Server 2012 :: How To Preserve Tags In Varchar Output

May 6, 2015

I have following XQuery:

declare @xmldoc as xml
select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>'
select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"

My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?

View 2 Replies View Related

SQL Server 2012 :: Converting Year And Month Varchar To Date

Jan 31, 2014

I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.

Year Month Productname
2013 11 ACB
2013 11 CDE

I would now like to add another column called date and store these Year Month as a date to my existing table

Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01

Is there a way I can do it for all the columns of the existing table ??

View 3 Replies View Related

SQL Server 2012 :: Explicit Casting Varchar And Nvarchar Columns?

Feb 26, 2014

I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.

But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?

For instance:

cast(a.varchar as nvarchar(100)) = an.nvarchar

or

cast(an.nvarchar as varchar(100)) = a.varchar

Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar ń, what's the best way to handle this?

Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.

Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?

View 8 Replies View Related

SQL Server 2012 :: Arithmetic Overflow Error For Type Varchar

Mar 11, 2014

I have the following code in a SP. The 2 if statements for the @@ Error I added only for testing as I suddenly started get the following error at the 1st @@Error statement

Msg 232, Level 16, State 2, Line
Arithmetic overflow error for type varchar, value = 10000.00000.

I cannot have this SP running minutes it must be done in seconds.

I have tried the folloiwng changes but then it takes to long.

-- set @new = 'x' + right('0000' + convert(varchar(5), round(rand() * 10000, 0)), 4)
-- set @new = 'x' + right('0000' + convert(varchar(4), right(round(rand() * 10000, 0),4)), 4)

declare @newvarchar(5),
@xstsint,
@new_retvarchar(5)
,@Cnt as int /* just for my testing */
set @cnt = 0 /* just for my testing */

[Code] ......

View 6 Replies View Related

SQL Server 2012 :: Can Change Column Width Of Varchar In Production

Oct 17, 2014

I keep getting requests to increase the width of a varchar colum every now and then.

I want to ask if its perfectly ok when you have active users connecting to the application to do this?

View 7 Replies View Related

SQL Server 2012 :: Script To Separate Note Column (varchar)

Jul 29, 2015

any tricks to separate this column from:

"testlname,testfname comment section > comment section testlname,testfname 6/30/2015 9:05:58 AM > testlname,testfname 6/30/2015 9:06:18 AM > comment section testlname,testfname 6/30/2015 9:10:05 AM > comment section"

to:

col1 Col2 col3
testlname,testfname 6/30/2015 9:05:58 AM comment section
testlname,testfname 6/30/2015 9:06:18 AM comment section
testlname,testfname 6/30/2015 9:10:05 AM comment section

View 1 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Varchar Value To Data Type Int

Aug 12, 2014

I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.

I am also including example of how the account_numbers are formatted.

R222209
R222220
R222222
R222212
R221123
F707768

[Code] .....

View 5 Replies View Related

SQL Server 2012 :: Conditional Logic Function To Return VARCHAR Value With Gender

May 4, 2015

I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.

SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO

Below is the conditional logic function I'm trying to create to replicate the logic above.

CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)

[Code] .....

View 6 Replies View Related

SQL Server 2012 :: Float Value Converting To Exponential While Inserting To Varchar Field?

Aug 6, 2015

Am converting varchar field to float and summing using group by and next inserting to varchar field(table).

while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726

My question is why it is converting while inserting ? and how to avoid it.

select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue

View 4 Replies View Related

Varchar, Convert In Sql

Feb 21, 2008

I am trying to run this statement
Select Calims, ProCode From Inquiry Where ProCode Between 80000 and 89999

ProCode is a varchar but I am still checking a range on it.
It seem to work fine but then there is some invalid data entry in this column
for example 'abace' or '100i' so I get the error.
1) how I can exclude them out of my query.
Someone suggested the following but I dont understand how do achieve that. thanks



" However, there would appear to be different data types. If Value is a character
--based type then you may want to limit the search by first extracting numeric values
-- with something like ISNUMERIC(Value) = 1 and then converting them to numerics using
--the convert function - CONVERT(int, Value) BETWEEN 10000 and 20000."

View 7 Replies View Related

SQL Server 2012 :: Case Statement On Nvarchar With Literal String Comparison To Varchar?

Apr 14, 2015

how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.

Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise

in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.

View 3 Replies View Related

SQL Server 2012 :: Find A Specific String And Replace It With Another Inside Of VARCHAR Field

Aug 14, 2015

I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.

UPDATEtbl
SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000)
FROM Table tbl
JOIN Person p ON p.PersonID = tbl.PersonID
JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2
WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%

'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.

View 5 Replies View Related

SQL Server 2012 :: Convert Parts Of String To Int

May 2, 2014

I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".

I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.

CREATE TABLE #tmp(duration VARCHAR(20))

INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec

And this is what I have so far

SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0

[Code] ...

I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.

View 3 Replies View Related

SQL Server 2012 :: Convert Integer To Date

Oct 16, 2014

In VBA, CLng(Now) will return the integer portion of a date CLng(Now) returns 41928, CDate(41928) then returns 10/16/2014. Is there something equivalent in SQL Server that will allow me to convert an integer value to a date?

In short, how can I convert a 100 year date to Gregorian (any format)?

View 3 Replies View Related

SQL Server 2012 :: Convert Integer To Time?

Dec 3, 2014

I've got a column that holds a time value in Integer form as a number from midnight (IE military time) with no leading 0s or colons.

0 = 00:00
30 = 00:30
...

1700 = 1700 etc...

How can I convert this to a real time field?

View 9 Replies View Related

SQL Server 2012 :: Using CAST Or Convert Functions

May 31, 2015

How do I use the CAST or CONVERT function in the code below, I require a third column (named Diff) which Minus the StartTime from the EndTime and the result is outputted in the third column (named Diff).

Calculation: @Diff = (@EndTime - @StartTime)

I still want the variables (@StartTime and @EndTime) to remain as nvarchar.

The code:

DECLARE @StartTime nvarchar(10) = '12:10';
DECLARE @EndTime nvarchar(10) = '12:30';
DECLARE @Diff time(1) = '00:00';
SELECT @StartTime AS '@StartTime', @EndTime AS '@EndTimes', @Diff AS '@Diff';

View 7 Replies View Related

How To Convert VARCHAR To TEXT

Mar 18, 2004

Hi all
iam trying to but a varchar variable into a TEXT var but i get this error "The assignment operator operation cannot take a text data type as an argument"

anybody know what shall i do
best regards

View 7 Replies View Related

Convert Varchar To Datetime!!!

Apr 20, 2005

Hi:
I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime. 
I use Query like:
SELECT
CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +CAST(SUBSTRING(date_sent,4,2) as int) + '/' +CAST(SUBSTRING(DATE_SENT,7,4) as int)
From MyTable
It is not working, anybody can give me some advise!
thanks!
 
 

View 4 Replies View Related

Convert Varchar To Number

Apr 25, 2001

Hi

In my MSSSQL database I have a table that contains "AGE" and its datatype is varchar.

I want to calculate the min(age) and max(age).

But the AGE field's datatype is varchar and I canot do any mathematical functions.



In Oracle There is a code for doing that.I think this is the code


select max(tonumber(age));


what is it's MSSQL equivalent.


thanks

sabu

View 2 Replies View Related

How Can I Convert Varchar Value To Integer

Mar 13, 2002

hi i want to insert the varchar value inter column, how can i convert the values and insert in to the record if i give direct column name i am getting a message
insert into EMP(id)
SELECT name from STU

i am getting a message like this
Syntax error converting the varchar value '200.00' to a column of data type int.

View 3 Replies View Related

How To Convert Varchar Datatype To Int??

Jul 2, 2007

Hi all,

I am using a varchar datatype for PIN number to handle zero at start. Now i want to do mathematical calculation to encrypt the PIN so i need to convert that varchar datatype to int so that zero should not be discarted after conversion. i.e. 0123 and 123 must not be treated as same PIN.

Please kindly give me a way out. I am using RSA encryption.

Thanks in anticipation.

Haider Abbas.

View 5 Replies View Related

Convert Varchar To Number

Dec 4, 2007

Using SQL 2005. I have a field with dollar amounts but field type is VarChar. Need to convert to number to sum. What's the best way to do this or how can I sum a field type Varchar that has dollar amounts. Thank you, David

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved