strange type conversion error with datetime...

microsoft.public.sqlserver.programming

21 Dec 2006 6:39 PM

Tim Mackey

hi,
i've spent ages puzzling over this, and read similar situations on the net
but it's usually a different problem of people using 'dd mm yyyy' format
instead of 'dd mmm yyyy'. using sql 2005.

i have a very straight forward stored procedure, with a datetime parameter
set to default to GetDate

CREATE PROCEDURE [dbo].[RestaurantLog_INSERT]
(
@Restaurant int,
@Audit int,
@Action NVarChar(400),
@Username NVarChar(50),
@Comments NVarChar(400),
@Date DateTime = GetDate
)
AS
BEGIN

INSERT INTO RestaurantLog
     ( [Restaurant], [Audit], [Action], [Username], [Comments],
[Date] )
VALUES
( @Restaurant, @Audit, @Action, @Username, @Comments, @Date )

END

when i try to execute it with the following values, i get a type conversion
error.

EXECUTE [dbName].[dbo].[RestaurantLog_INSERT]
   1, 1, 'Test', 'Test', 'Test', GetDate

here's the error, driving me nuts!
Msg 8114, Level 16, State 5, Procedure RestaurantLog_INSERT, Line 0
Error converting data type nvarchar to datetime.

the table spec is as follows:
CREATE TABLE [dbo].[RestaurantLog](
     [Restaurant] [int] NOT NULL,
     [Audit] [int] NULL,
     [Action] [nvarchar](400) COLLATE Latin1_General_CI_AS NULL,
     [Username] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
     [Comments] [nvarchar](400) COLLATE Latin1_General_CI_AS NULL,
     [Date] [datetime] NULL CONSTRAINT [DF_RestaurantLog_Date] DEFAULT
(getdate())
) ON [PRIMARY]

the execute statement works if i put in a Date like '14 Dec 2006' but it
won't work when i supply the SP with GetDate, which is supposed to be of
type DateTime, so why the conversion error, where is it getting nvarchar
from???

thanks to anyone who can help
tim