format phone number in query

You are at right direction, so what is your problem?
src field is varchar?

Thanks,
Rajni Shekhar

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

i inserted like this
7032442030

but

i want to insert value like this,

eg
+1-703-244-2030

----------------------table-----------------------------
create table mytable(calldate varchar(200),phone varchar(300))


---------------------procedure-----------------------------



create procedure myproc

as

Begin

DECLARE @variable VARCHAR(50)

DECLARE @sqlQuery VARCHAR(8000)

DECLARE @finalQuery VARCHAR(8000)

Declare @LastUpdate_calldate varchar(200)

if isnull(@LastUpdate_calldate,'0')='0'

BEGIN
set @LastUpdate_calldate=replace(convert(varchar,getdate()-510,102),'.','-') + ' 00:00:00.000'

END

ELSE

BEGIN

set @LastUpdate_calldate=replace(convert(varchar,getdate(),102),'.','-') + ' 00:00:00.000'

END

Print @LastUpdate_calldate



SET @sqlQuery = 'SELECT DATE_FORMAT(calldate,''''%Y%-%m-%d %H:%I:%s.000'''') as calldate,src FROM mytable

where src in (7031442029, 7031442030,7031442069)
and
DATE_FORMAT(calldate,''''%Y%-%m-%d %H:%I:%s.000'''') > ' + '''' + '''' + @LastUpdate_calldate + '''' + ''''


SET @finalQuery = 'SELECT * FROM OPENQUERY(LNKMY,' + '''' + @sqlQuery + '' + ' Order By calldate desc ''' + ')'

SELECT @finalQuery


INSERT INTO MYTABLE (calldate,src)


EXEC(@finalQuery)

End

please modify this code,
thanks.

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

create procedure myproc 



as 



Begin 



DECLARE @variable VARCHAR(50) 



DECLARE @sqlQuery VARCHAR(8000) 



DECLARE @finalQuery VARCHAR(8000) 



Declare @LastUpdate_calldate varchar(200) 



if isnull(@LastUpdate_calldate,'0')='0' 



BEGIN 

set @LastUpdate_calldate=replace(convert(varchar,getdate()-510,102),'.','-') + ' 00:00:00.000' 



END 



ELSE 



BEGIN 



set @LastUpdate_calldate=replace(convert(varchar,getdate(),102),'.','-') + ' 00:00:00.000' 



END 



Print @LastUpdate_calldate 







SET @sqlQuery = 'SELECT DATE_FORMAT(calldate,''''%Y%-%m-%d %H:%I:%s.000'''') as calldate,(''''+1-'''' +left( src , 3 )+ ''''-'''' + substring ( src , 4 , 3 )+ ''''-'''' + substring ( src , 7 , 4 )) as phone FROM mytable 



where src in (7031442029, 7031442030,7031442069) 

and 

DATE_FORMAT(calldate,''''%Y%-%m-%d %H:%I:%s.000'''') > ' + '''' + '''' + @LastUpdate_calldate + '''' + '''' 





SET @finalQuery = 'SELECT * FROM OPENQUERY(LNKMY,' + '''' + @sqlQuery + '' + ' Order By calldate desc ''' + ')' 



SELECT @finalQuery 





INSERT INTO MYTABLE (calldate,src) 





EXEC(@finalQuery) 



End 

Thanks,
Rajni Shekhar

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

i run the sp but,

error happen

OLE DB provider "MSDASQL" for linked server "LNKVOICE" returned message "[MySQL][ODBC 3.51 Driver][mysqld-5.0.77]FUNCTION myvoicedb.substring does not exist".

please correct the error

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

try to run it.

select ('+1-' +left( '7032442030' , 3 )+ '-' + substring( '7032442030' , 4 , 3 )+ '-' + substring ( '7032442030' , 7 , 4 )) as phone

What database you are using? if it is sql server, this syntax will run else you need to find out the substring function syntax of your database.

Thanks,
Rajni Shekhar

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

i used IN keyword to src

where src in (7031442029, 7031442030,7031442069)

because
this query is not used
select ('+1-' +left( '7032442030' , 3 )+ '-' + substring( '7032442030' , 4 , 3 )+ '-' + substring ( '7032442030' , 7 , 4 ))


any changes in the line (MYSQL)

(''''+1-'''' +left( src , 3 )+ ''''-'''' + substring ( src , 4 , 3 )+ ''''-'''' + substring ( src , 7 , 4 )) as phone

please correct this,

or any changes to insert query that is SQL SERVER

please give the solution,

thanks.

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator