Re: datetime convert error

Diana — Thu, 30 Jul 2009 11:20:41 GMT

O intrebare - pana la urma unde folosesti @cqfd?
Pentru lucrul cu date, vezi si http://sqlserver.ro/forums/1/7154/ShowThread.aspx - in special sfatul lui Razvan.
Pentru "dynamic sql", vezi http://www.sommarskog.se/.

Re: datetime convert error

noface — Thu, 30 Jul 2009 11:13:12 GMT

dap, intr-e timp mi-am dat seama ca escapam aiurea, de acolo era si problema aparent. Multumesc mult pt indrumari

Re: datetime convert error

Diana — Thu, 30 Jul 2009 11:08:40 GMT

....si nu uita sa adaugi apostroafele necesare - de exemplu:
DECLARE @str varchar(100)
SET @str = 'SELECT * FROM Category WHERE CreatedOn BETWEEN ' + '''' + CONVERT(varchar(25), @date, 101) + '''' +.....

Re: datetime convert error

noface — Thu, 30 Jul 2009 10:53:10 GMT

am incercat si asta anterior, era de forma convert(varchar(25),......,101); dar nici asa nu a mers.
ce e ciudat e ca daca rulez separat insertul, fara set @dinstring='.....' si exec (@dinstring),functioneaza fara probleme

Re: datetime convert error

Diana — Thu, 30 Jul 2009 10:44:39 GMT

Incearca, la formarea @dinastring, sa convertesti in mod explicit @firstDayOfCurentMonth si @mydate la "varchar". Vezi si BOL ---> Data type precedence.

datetime convert error

noface — Thu, 30 Jul 2009 09:33:18 GMT

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

Intrebari generale

Re: datetime convert error

Re: datetime convert error

Re: datetime convert error

Re: datetime convert error

Re: datetime convert error

datetime convert error