Few of the questions I receive very frequently. I have collect them in spreadsheet and try to answer them frequently.
How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.
How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table
Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.
Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.
How to parse/retrieve only numbers from column values contains alpha-numeric characters?
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
What are the online references for CAST and CONVERT?
CAST and CONVERT
Reference : Pinal Dave (https://blog.sqlauthority.com)
439 Comments. Leave new
hello,
help please,
i have a datetime variable named ‘date’ , i want to save it to sql database.
when i save try to save to database it is no longer 10/06/2011 (june, ten), it is become 06/10/2011 (october, six)
what an i doing wrong? i am using c#
this is how i get the date:
string date = DateTime.Now.ToString(“dd/MM/yyyy”);
also this is my first time trying to create a program, please explaine as much as you can.
thanks a lot, Boosh.
You should always use unambigious date formats. Refer this to know more about this
Hi Dave, I looked into this blog a lot, my regards for a very good job.
I checked the blog to see if may question had been answer before but no luck
:(
this is it:
I need to search for values in a column which is composed of Julian dates,
the values have this mask: [Y][ddd][XXXXXX] so the actual value would be: 8092800033 which corresponds to 04/01/2008 that
I need to select values in a range for instance
where 1=1
and (x like ‘8092%’ or x like ‘8093%’ or x like ‘8093%’)
corresponding a 3 different days, from 04/01/2008 to 04/03/2008
my question to you is, how can I use the like operator or any other operator to avoid placing every number but rather a range, since something as:
where 1=1
and x like ‘[8092 -8093]%’
didn’t work for me?
do you have any insights on this?
hi im trying to convert to integer so when i put this in it doesnt round down
DATEDIFF(mi, dbo.JobTracking.JobCallTime, dbo.BillyCalendarTable.[Time Finished]) / 60
hi,
i have a table like this:-
cardId issusedate
1 2009-01-18 10:15:42.000
2 2009-01-18 10:15:49.000
3 2009-02-11 08:43:51.000
4 2009-03-18 11:15:44.000
5 2009-03-18 11:15:52.000
6 2009-03-18 11:16:11.000
7 2009-04-18 11:16:19.000
8 2009-04-11 09:15:02.000
9 2009-05-18 01:10:23.000
10 2009-05-18 01:10:42.000
11 2009-06-18 01:10:50.000
12 2009-06-18 01:11:09.000
13 2009-06-18 01:11:17.000
14 2009-06-18 01:11:36.000
i want output like this:-
jan feb mar april may june
2 1 3 2 2 4
number of card issues, monthwise…
thanks
Abhishek
Look at this PIVOT post
hi. i need your help in sorting numbers.
i have these numbers in numbering field :
3.1,3.2,3.3,3.4,……,3.11, 3.12
and i want it to be displayed as these:
3.1
3.2
3.3
..
..
3.11
3.12
the problem is, it doesn’t appear that way.
so i used CAST.
e.g:
CAST ([numbering] AS DECIMAL (4,2)) as NUM
–ORDER BY NUM ASC;
and it becomes:
3.10
3.10
3.11
3.20
3.30
how can i make it to be displayed this way? =>
3.1
3.2
3.3
..
..
3.11
3.12
pls help. thanks.
This is more of the formation issue that should be done in the front end application.
You can replace . with ,
then convert
I need help!! I am trying to convert the following data that I am pulling from a database:
Date Time Duration
20110718 30000 405
And I want it to look like:
Date Time Duration
07/18/11 3:00:00 AM 00:04:05
I keep encountering the following error message: “Conversion from type ‘Integer’ to type ‘Date’ is not valid.”
Is there a way to convert this data?
What are the datatype of these columns?
All three are (int, not null).
Try this
declare @t table(Date int, Time int , Duration int)
insert into @t
select 20110718 ,30000 ,405
select cast(cast(date as CHAR(8)) as datetime) as date,stuff(stuff(right(‘000000’+cast(time as varchar(100)),6),5,0,’:’),3,0,’:’) as time,
stuff(stuff(right(‘000000’+cast(Duration as varchar(100)),6),5,0,’:’),3,0,’:’) as duration from @t
Hi All,
I need some help. I’m trying to caluculate some overtime. Our system logs the start time and end time of each taks. I need to know who finishes after 17:00 and how many minutes/hours they have done after 17:00.
So far I managed to extrat the time, but it is return in Char and cannot make any calculation.
Thanks for your help.
SELECT dbo.Staff.full_name AS Name, dbo.Tasks.title AS Taks, dbo.Project.title AS Project, dbo.Worktrans.tran_date AS Date,
dbo.Worktrans.work_description AS Description, CONVERT(varchar(10), dbo.Worktrans.start_time, 108) AS StartTime, CONVERT(varchar(10),
dbo.Worktrans.end_time, 108) AS EndTime
Hello,
I would like assistance in assigning a certification number using the CAST SQL command.
Below is the snippet of the code that assigns the certification number:
UPDATE Cert SET TrackingNo = CTypeID + ‘-‘ + CAST(@ApplicantNo AS VARCHAR)
While the above works for my organization, I would like to know how to assign certification numbers using a 5-number format. The code above assigns certification numbers starting with 1 and up, i.e. R-1 to R-5497. I am looking to have R-11111 assigned and so on with all the numbers being 5 digits.
Thank you for your assistance.
Scott
hello,
i have to use between query to select date with format(100). but i cant convert it in 100 format. plz help me
i have to convert date through UI
Thanks for this valuble information
Hi Sir,
I want to convert money into words throw SQL PROCEDURE Like:-29872.50 in Words Twenty Thous. Eight Hund. and Fifty Paise Only
Can Any one Help me
Thank You
i want to convert numeric values in word using function in oracle,plz help
eg:20134
twenty thousand one hundred thirty four
This site is for MS SQL Server. For Oracle queries post your questions at oracle related sites
hi please help
i am getting the following error im new on SQL
–Msg 8115, Level 16, State 2, Line 11–
–Arithmetic overflow error converting expression to data type datetime.–
select a.*,WAGETYPE,DRREPAYMENTMETHOD,LASTSTRIKEDATE,SALARYDAY,lastreceiptdate into #1
from mis..ptpreport_detail a left join EDWDW..tbdebitorder b (nolock)
on a.loanrefno=b.LOANREFERENCE
where DRREPAYMENTMETHOD=’FLD’ and a.Status=’Pending’ and RepayMethod=’EFT’
and TemplateName in (‘CCConsultant’,
‘NewCallCentreWorkFlow’,
‘Call Centre Agent Super User’)
select distinct a.loanref,a.[Narration 1],b.WAGETYPE,cast(CONVERT(varchar(10),b.LASTSTRIKEDATE,112) AS datetime) LASTSTRIKEDATE,b.ptpduedate,
PTPMAdeDate,failedreasoncode1, b.lastreceiptdate, c.lastreceiptdate
from EDWDW..vw_AbilNetreceiptsAllUnion a left join EDWDW.#1 b (nolock)
on a.loanref=b.loanrefno
left join PhaseII..ACCOUNTDETAILS (nolock)
on a.loanref=PhaseII..ACCOUNTDETAILS.loanrefno
left join EDWDW..tbdaily c(nolock)
on a.loanref=LoanRefId
where a.valuedate between convert(varchar(8),b.PTPMadeDate,112) and
convert(varchar(8),b.PTPDueDate,112)
and a.Amount>0 and B.ptpduedate>getdate()
and c.lastreceiptdate > ‘2011-09-01 00:00:00.000’
and failedreasoncode1 is null
and b.LASTSTRIKEDATE =b.PTPMAdeDate
and [narration 1] not in (‘Payment Stopped’,’Debits Not Allowed’,
‘Debit In Contravention’,’Authorisation Cancelled’,’Debits Not Allowed’,
‘Account Frozen’,’No Authority To Debit’,’Previously Stopped’,’No Such Account’,
‘Account Closed’)
What is the datatype of the column LASTSTRIKEDATE?
how to change number to words.
for example, 100 to hundred.
I have this exemple(SQL Server 2008):
SET XACT_ABORT OFF
BEGIN TRY
BEGIN TRANSACTION
PRINT convert(int,’abc’)
–insert …
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ‘Convert fails’
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION
print ‘ROLLBACK’ — <————————-
END
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION
PRINT 'COMMIT'
END
END CATCH
When the convert fails inside a BEGIN TRY…CATCH the transaction cannot be committed (always end on print 'ROLLBACK' ). Is there any way to solve this?
Como faço p converter esse inteiro em string; SEELCT * FROM TABELA WERE indice = Convert(int,”digite o indice:”);
INSERT INTO Table1([Language],[Value],[ShortDescription],[LongDescription],[DisplayOrder])VALUES(‘English’,’None’,null,’one’,1)
Value column is primarykey and not allow null
When I am trying to insert its shows “Error converting data type varchar to float.” If i use any string value it’s not working(‘None) in the value column .If I use numeric value it’s inserting(‘123’).How to resolve this
Im trying to set a feild that is a string from a integer feild how can i do this:
set refno=batchno
where refno is actually a string(text feild)
Hi, this is an amazing blog. Well … I’m with a little problem with the conversion of a text string to number. Mainly the problem is that the string can come dirty. For example ‘123 /. where always the “dirt” is abut the beginning or end of the string, but it is not its length.
The errors I get is this:
Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value”123 ‘to a column of data type int.
Without traversing the string, letter by letter and without cursors, there is a solution “elegant” and simple for this problem?
Thank you very much!
sanjay
Your comment is awaiting moderation.
HI,
CAN ANYBODY HELP FOR BELOW ERROR.
THIS IS MY CODE
DECLARE @i INT
DECLARE @COUNT VARCHAR(8)
DECLARE @RowCount INT
DECLARE @Query Varchar(100)=”
SELECT @Query = ‘SELECT COUNT(*) FROM @Emp’
SET @i=1
DECLARE @Emp Table(IdEmp INT Primary Key Identity(1,1),Id INT )
INSERT INTO @Emp SELECT DISTINCT AddressId FROM Tb_Employee
Select * from @Emp
SET @RowCount= CAST ((@Query) As INT)
Here I Am Coverting @Query To INT. But I geeting Error
As Conversion failed when converting the varchar value ‘SELECT COUNT(*) FROM @Emp’ to data type int.
Instead of
SET @RowCount= CAST ((@Query) As INT)
use
SET @RowCount= (SELECT COUNT(*) FROM @Emp)
Hi Madhivanan
Thank U For The Reply It Worked For ME