Converting datetime from character string error 2

[Viruland]

Hi,

I'm new to MS SQL and I'm trying to retrieve data from a certain table. I've wrote a SP but I always get a converting datetime from character string error. I don't now how I can fix the problem.


USE EDOC

/* Via deze Stored Procedure gaat men de Status gaan aanpassen van toestand Passief naar Open voor alle workflowtabellen.
Indien de geplande afhandelingsdatum binnen een bepaalde tijdspanne ligt wordt de Status van toestand verandert.
*/

GO

/* Nakijken of de Stored Procedure al bestaat.
Is dit het geval dan wordt deze eerst verwijderd om opnieuw aangemaakt te worden.
*/

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'usp_UpdateWorkflowStatus' AND type = 'P')
DROP PROCEDURE usp_UpdateWorkflowStatus
GO

CREATE PROCEDURE usp_UpdateWorkflowStatus
@tabelNaam nvarchar(150) -- variabele tabelnaam die gebruikt wordt in het SQL Update statement

AS

DECLARE @Query varchar(255)

SET @Query = 'UPDATE ' + RTRIM(@tabelNaam) + ' SET STATUS = 1 WHERE ((Status = 3)'

IF (@tabelnaam <> 'tblCPA' AND @tabelNaam <> 'tblAudit')
BEGIN
SET @Query = @Query + ' AND (GeplAfhDatum < ' + GetDate() + '))'
EXEC (@Query)
END
ELSE
BEGIN
SET @Query = @Query + ' AND (GeplAfhDatum < ' + DateAdd(&quot;d&quot;, 90, GetDate())+ '))'
EXEC (@Query)
END


Live fast, die young and leave a beautiful corpse behind.

 

[Glyndwr]

Viruland,

you need to enclose the date in quotes

try the following changes

1) SET @Query = @Query + ' AND (GeplAfhDatum < ''' + GetDate() + '''))'

2) SET @Query = @Query + ' AND (GeplAfhDatum < ''' + DateAdd(&quot;d&quot;, 90, GetDate())+ '''))'

Glyndwr

 

[nigelrivett]

What format is GeplAfhDatum ?

you maybe want something like

AND (GeplAfhDatum < ''' + convert(varchar(8),GetDate(),112) + ' ' + convert(varchar(8),getdate(),8) + ''')'

Try selecting the statement instead of execing it and you will probably see the problem.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Viruland]

nigelrivett,

It works well but I don't know how I must write the second part of my If function

SET @Query = @Query + ' AND (GeplAfhDatum < ' + DateAdd(&quot;d&quot;, 90, GetDate())+ '))'

If it is no problem for you can you help me out.

In your conversion function you uses the numbers 112 and 8, what is the purpose of these numbers

Live fast, die young and leave a beautiful corpse behind.

 

[nigelrivett]

That is to get the format yyyymmdd hh:mm:ss.
See convert in bol.
If you don't wan the time then leave out the second convert.


SET @Query = @Query + ' AND (GeplAfhDatum < ''' + convert(varchar(8),DateAdd(d, 90, GetDate()),112)+ '''))'

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JamesLean]

There's no need to concatenate the getdate() value separately. This should do the same thing:

CREATE PROCEDURE usp_UpdateWorkflowStatus
  @tabelNaam nvarchar(150)    -- variabele tabelnaam die gebruikt wordt in het SQL Update statement
AS

DECLARE @Query varchar(1000)

SET @Query = 'UPDATE ' + RTRIM(@tabelNaam) + '
SET Status = 1
WHERE Status = 3'

IF (@tabelnaam <> 'tblCPA' AND @tabelNaam <> 'tblAudit')
  SET @Query = @Query + ' AND GeplAfhDatum < GetDate()'
ELSE
  SET @Query = @Query + ' AND GeplAfhDatum < GetDate() + 90'

EXEC(@Query)

--James

 

[JamesLean]

You could even shorten it to:

DECLARE @Query varchar(1000)

SET @Query = 'UPDATE ' + RTRIM(@tabelNaam) + '
SET Status = 1
WHERE Status = 3
  AND GeplAfhDatum < GetDate()'

IF @tabelnaam IN ('tblCPA', 'tblAudit')
  SET @Query = @Query + ' + 90'

EXEC(@Query)

--James