SQL Server - insert format

hi friends,
Now i inserted like this
1035442030

but
 i want insert src field in this format

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

eg
103-544-2032


where to apply this above format in this procedure,

please modify the procedure,

thanks.

this is full 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 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

Hello,

 

Added your code as yellow backround color code

 

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

 

LEFT(src , 3 )+ '-' + SUBSTRING(src , 4 , 3 )+ '-' + SUBSTRING(src , 7 , 4 ) as src

 

FROM mytable where 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)

 

Hope this helpful!

Thanks

 

thanks,

but,



error happens,

Msg 8117, Level 16, State 1, Procedure myproc, Line 14
Operand data type varchar is invalid for subtract operator.


LEFT(src , 3 )+ '-' + SUBSTRING(src , 4 , 3 )+ '-' + SUBSTRING(src , 7 , 4 ) as src

but i want  like this with country code
'+1-'


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

please give the solution.


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 + '''' + ''''


full 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

Hello,

Try as following way

Declare @src as varchar(max) = '10354420312'

 

select left(@src , 3 )+ '-' + substring ( @src , 4 , 3 )+ '-' + substring ( @src , 7 , 4 )

 

SET @src = '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 ) src

 

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

Hope this helpful!

Thanks

 

SET @src = '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 ) src

 

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

sorry

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.



please go through the query,


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 src

FROM mytable


where src in (7031442029, 7031442030,7031442069)

and

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


please correct this.

hi,

Try this example query:

select case when len(ltrim(rtrim(User_Phone)))='10' then '('+SUBSTRING(User_Phone,1,3)+')'+' '+SUBSTRING(User_Phone,4,3)+'-'+SUBSTRING(User_Phone,7,4) when len(ltrim(rtrim(User_Phone)))='' then ' ' end AS User_Phone from users

Hopw it helps!!!!!!!!!!!!!!!!!!!!!


Regards,

hi,

Try this example query:

select case when len(ltrim(rtrim(User_Phone)))='10' then '('+SUBSTRING(User_Phone,1,3)+')'+' '+SUBSTRING(User_Phone,4,3)+'-'+SUBSTRING(User_Phone,7,4) when len(ltrim(rtrim(User_Phone)))='' then ' ' end AS User_Phone from users

Hopw it helps!!!!!!!!!!!!!!!!!!!!!


Regards,

Incorrect syntax near '10'.


please correct this.

please remove single quate because 10 is an integer.

Hello,

 

Try to use concat function  as following way

 

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

 

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

 

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

 

  

 

 

 

 

Hope this helpful!

Thanks