[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Error converting data type nvarchar to datetime

Orgil

3/20/2007 12:16:00 PM

USE SUTDNet

DECLARE @id nvarchar(14), @err int

SELECT @id = 'CD1122BOM00001'
exec @err = DoCDArchive 1, @id, GETDATE, 'Archive for Example'

Why do I get an error "Server: Msg 8114, Level 16, State 4, Procedure
DoCDArchive, Line 0
Error converting data type nvarchar to datetime." (I use MSSQL2000)

-----------------------------------------------------------------------------------------
The stored procedure is:

CREATE PROCEDURE DoCDArchive
@ID_User int,
@ID_CD nvarchar(14),
@Date datetime,
@Desc nvarchar(200) = NULL
AS
DECLARE @err int
IF EXISTS ( SELECT CD_ID FROM S_CD WHERE CD_ID = @ID_CD ) BEGIN
BEGIN TRAN
INSERT INTO B_ArchiveInfo (ID_CD, ArchivedDate, [Desc]) VALUES
(@ID_CD, @Date, @Desc)
IF @@ERROR > 0 BEGIN
ROLLBACK TRAN
RETURN @@ERROR
END
ELSE
COMMIT TRAN
-- Get ID of Ezemshigch
DECLARE @ID_Ezemshigch int
SELECT @ID_Ezemshigch = Value FROM A_Params WHERE Name =
'Ezemshigch'
-- Add action to Logs
Exec @err = AddLog @ID_Ezemshigch, @ID_User, 6, @ID_CD
END
return @err
GO
4 Answers

Adi Cohn

3/20/2007 12:19:00 PM

0

On Mar 20, 2:15 pm, "Orgil" <orgi...@yahoo.com> wrote:
> USE SUTDNet
>
> DECLARE @id nvarchar(14), @err int
>
> SELECT @id = 'CD1122BOM00001'
> exec @err = DoCDArchive 1, @id, GETDATE, 'Archive for Example'
>
> Why do I get an error "Server: Msg 8114, Level 16, State 4, Procedure
> DoCDArchive, Line 0
> Error converting data type nvarchar to datetime." (I use MSSQL2000)
>
> ---------------------------------------------------------------------------?--------------
> The stored procedure is:
>
> CREATE PROCEDURE DoCDArchive
> @ID_User int,
> @ID_CD nvarchar(14),
> @Date datetime,
> @Desc nvarchar(200) = NULL
> AS
> DECLARE @err int
> IF EXISTS ( SELECT CD_ID FROM S_CD WHERE CD_ID = @ID_CD ) BEGIN
> BEGIN TRAN
> INSERT INTO B_ArchiveInfo (ID_CD, ArchivedDate, [Desc]) VALUES
> (@ID_CD, @Date, @Desc)
> IF @@ERROR > 0 BEGIN
> ROLLBACK TRAN
> RETURN @@ERROR
> END
> ELSE
> COMMIT TRAN
> -- Get ID of Ezemshigch
> DECLARE @ID_Ezemshigch int
> SELECT @ID_Ezemshigch = Value FROM A_Params WHERE Name =
> 'Ezemshigch'
> -- Add action to Logs
> Exec @err = AddLog @ID_Ezemshigch, @ID_User, 6, @ID_CD
> END
> return @err
> GO

Try running this:
exec @err = DoCDArchive 1, @id, GETDATE(), 'Archive for Example'

Adi

Aaron [SQL Server MVP]

3/20/2007 12:24:00 PM

0

Because you can't pass GETDATE() into a parameter that way. You need to
either set it up first:

DECLARE @dt DATETIME;
SET @dt = GETDATE();
EXEC @err = dbo.DoCDArchive 1, @id, @dt, 'Archive for Example';

Or allow NULL in the parameter and do it inside:

ALTER PROCEDURE dbo.DoCDArchive
@ID_User INT,
@ID_CD NVARCHAR(14),
@Date DATETIME = NULL,
@Desc NVARCHAR(200) = NULL
AS
BEGIN
SET NOCOUNT ON;

SET @Date = COALESCE(@Date, GETDATE());
-- other stuff
END
GO

DECLARE @id NVARCHAR(14), @err INT;
SET @id = 'CD1122BOM00001';
EXEC @err = dbo.DoCDArchive 1, @id, NULL, 'Archive for Example';


--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...





"Orgil" <orgilhp@yahoo.com> wrote in message
news:1174392957.876679.291440@y66g2000hsf.googlegroups.com...
> USE SUTDNet
>
> DECLARE @id nvarchar(14), @err int
>
> SELECT @id = 'CD1122BOM00001'
> exec @err = DoCDArchive 1, @id, GETDATE, 'Archive for Example'
>
> Why do I get an error "Server: Msg 8114, Level 16, State 4, Procedure
> DoCDArchive, Line 0
> Error converting data type nvarchar to datetime." (I use MSSQL2000)
>
> -----------------------------------------------------------------------------------------
> The stored procedure is:
>
> CREATE PROCEDURE DoCDArchive
> @ID_User int,
> @ID_CD nvarchar(14),
> @Date datetime,
> @Desc nvarchar(200) = NULL
> AS
> DECLARE @err int
> IF EXISTS ( SELECT CD_ID FROM S_CD WHERE CD_ID = @ID_CD ) BEGIN
> BEGIN TRAN
> INSERT INTO B_ArchiveInfo (ID_CD, ArchivedDate, [Desc]) VALUES
> (@ID_CD, @Date, @Desc)
> IF @@ERROR > 0 BEGIN
> ROLLBACK TRAN
> RETURN @@ERROR
> END
> ELSE
> COMMIT TRAN
> -- Get ID of Ezemshigch
> DECLARE @ID_Ezemshigch int
> SELECT @ID_Ezemshigch = Value FROM A_Params WHERE Name =
> 'Ezemshigch'
> -- Add action to Logs
> Exec @err = AddLog @ID_Ezemshigch, @ID_User, 6, @ID_CD
> END
> return @err
> GO
>


Orgil

3/20/2007 12:28:00 PM

0

I've tried like this way (GETDATE()). But still an error occurs and
the error is " Incorrect syntax near ')' ".

Orgil

3/20/2007 12:36:00 PM

0

Thank you Aaron Bertrand, You were right. I never thought that
GETDATE() must be set first.