set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[Rap_3_2]
@orasP varchar(50)
AS
begin
SET NOCOUNT ON;
declare @mydate DATETIME, @firstDayOfPrevMonth datetime,@firstDayOfCurentMonth datetime,
@myyear varchar(100),@cqfd datetime,@numetabela varchar(1000),@createtable varchar(1000),@tn varchar(1000),
@dinstring varchar(4000),@dinstring2 varchar(4000)
set @mydate = GETDATE();
set @myyear=datepart(year,@mydate);
set @firstDayOfPrevMonth = DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-1,@mydate));
set @firstDayOfCurentMonth =DATEADD(dd,-(DAY(@mydate)-1),@mydate);
-------set CQFD(current quarter's first day)
if (@mydate between ''+@myyear+'-01-01' and ''+@myyear+'-03-31')
begin
set @cqfd = cast(''+@myyear+'-01-01' as datetime);
end
else if (@mydate between ''+@myyear+'-04-01' and ''+@myyear+'-06-30')
begin
set @cqfd = cast(''+@myyear+'-04-01' as datetime);
end
else if (@mydate between ''+@myyear+'-07-01' and ''+@myyear+'-09-30')
begin
set @cqfd = cast(''+@myyear+'-07-01' as datetime);
end
else if (@mydate between ''+@myyear+'-10-01' and ''+@myyear+'-12-31')
begin
set @cqfd = cast(''+@myyear+'-10-01' as datetime);
end
--------------------------------------------------
SET @numetabela = DATENAME(m, GETDATE())
+ CAST(DAY(GETDATE()) AS VARCHAR(2))
+ CAST(YEAR(GETDATE()) AS CHAR(4))
+ CONVERT(varchar,DATEPART(Hh,GETDATE()))
+ CONVERT(varchar,DATEPART(mi,GETDATE()))
+ CONVERT(varchar,DATEPART(ss,GETDATE()))
+ CONVERT(varchar,DATEPART(ms,GETDATE()))
print @numetabela
SET @createtable = 'CREATE TABLE ' + convert(varchar,@numetabela) + ' (
TipClient varchar(100),
Regiune varchar(50),
Oras varchar(50),
Cod varchar(50),
NumeClient varchar(100),
CodExt varchar(50),
Flag varchar(50)
)'
exec (@createtable)
set @dinstring = 'insert into'+ convert(varchar,@numetabela)+' (Regiune,Oras,Cod,NumeClient,CodExt,Flag,TipClient) select h.Regiune,h.Oras,h.Cod,h.Numeclient,h.CodExt,h.Flag,''Tip 1'' from history h where oras='+@orasP+' and flag=''1'' and datatelefon not between '+@firstDayOfCurentMonth+' and '+ @mydate+' union all select p.regiune,p.oras,p.Cod,p.numeclient,p.CodExt,p.flag,''Tip 1'' from std_db p inner join ext_db i on p.CodExt=i.CodExt where p.oras='+@orasP+' and p.flag=''1'' and i.datatelefon not between '+@firstDayOfCurentMonth+' and '+@mydate+'';
exec(@dinstring)
end
eroarea intoarsa(la insertul de mai sus) este :
Msg 241, Level 16, State 1, Procedure Rap_3_2, Line 62
Conversion failed when converting datetime from character string.
din pacate, dupa 2 ore, nu am reusit sa-mi dau seama de unde apare.Campul "datatelefon" este datetime. imi poate va rog arata cineva unde gresesc?
versiunea e sql 2005