The conversion of a char data type to a datetime data type resulted in an out-of-range datetime valu

[M. Bruil]

Hi,

This error occurs when I call a stored procedure (SQL Server 2005) from MS
Access (2007) VBA, using ADODB.

I've tried to execute the stored procedure directly from the management
studio:

up_WerknemerInsert 10551,666, 'test', 'te', '', 'M', 't', '', '',
'2008-04-19 2:05:06 PM', '', '',''

this works just fine. When I run it from VBA, then the error occurs. I've
tried a lot of variations of the date format:

dd-MM-yyyy
yyyy-MM-dd
MM-dd-yyyy
MM/dd/yyyy

and so on...

The weird thing is that when I run this VBA in older versions of MS Access,
it seems to run error-free. I changed the @GebDate parameter in the stored
procedure from DateTime to nvarchar and tried to solve it by converting the
string to date in the INSERT INTO, but no success...

This is my VBA snippet:
==================================================================================
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "up_WerknemerInsert"

cmd.parameters.Append cmd.CreateParameter("@Relatie_Ref", adInteger,
adParamInput, , .Relatie_ID)
cmd.parameters.Append cmd.CreateParameter("@SOFINr", adInteger,
adParamInput, , .SOFINummer)
cmd.parameters.Append cmd.CreateParameter("@WnNaam", adVarChar,
adParamInput, 50, .Naam)
cmd.parameters.Append cmd.CreateParameter("@Voorletters", adVarChar,
adParamInput, 50, .Voorletters)
cmd.parameters.Append cmd.CreateParameter("@TussenVgsl", adVarChar,
adParamInput, 50, .Tussenvoegsel)
cmd.parameters.Append cmd.CreateParameter("@Geslacht", adVarChar,
adParamInput, 1, .Geslacht)
cmd.parameters.Append cmd.CreateParameter("@PriveAdres", adVarChar,
adParamInput, 50, .Adres)
cmd.parameters.Append cmd.CreateParameter("@PrivePC", adVarChar,
adParamInput, 7, .Postcode)
cmd.parameters.Append cmd.CreateParameter("@PriveWnPlaats", adVarChar,
adParamInput, 30, .Woonplaats)
cmd.parameters.Append cmd.CreateParameter("@GebDat", adDate, adParamInput, ,
"2008-04-19 2:05:06 PM")
cmd.parameters.Append cmd.CreateParameter("@Email", adVarChar, adParamInput,
50, .Email)
cmd.parameters.Append cmd.CreateParameter("@Rekening_nr", adVarChar,
adParamInput, 11, .Rekeningnummer)

Dim p As New ADODB.Parameter
Set p = cmd.CreateParameter("@WerknemerID", adVarChar, adParamOutput, 30)
cmd.parameters.Append p

cmd.Execute

..Werknemer_ID = p.value



==================================================================================

And this is the stored procedure I've written:

==================================================================================

GO

/****** Object: StoredProcedure [dbo].[up_WerknemerInsert] Script Date:
03/25/2009 20:10:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



ALTER PROCEDURE [dbo].[up_WerknemerInsert]
@Relatie_Ref int,
@SOFINr int,
@WnNaam nvarchar(50),
@Voorletters nvarchar(50),
@TussenVgsl nvarchar(50),
@Geslacht nvarchar(1),
@PriveAdres nvarchar(50),
@PrivePC nvarchar(7),
@PriveWnPlaats nvarchar(30),
@GebDat nvarchar(30),
@Email nvarchar(50),
@Rekening_nr nvarchar(11),
@WerknemerID nvarchar(30) output

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO tblWerknemer
(
Relatie_Ref,
SOFINr,
WnNaam,
Voorletters,
TussenVgsl,
Geslacht,
PriveAdres,
PrivePC,
PriveWnPlaats,
GebDat,
Email,
Rekening_nr
)
VALUES
(
@Relatie_Ref,
@SOFINr,
@WnNaam,
@Voorletters,
@TussenVgsl,
@Geslacht,
@PriveAdres,
@PrivePC,
@PriveWnPlaats,
CONVERT(DATETIME,@GebDat,104), --'18/02/2009
11:24:34',104),
@Email,
@Rekening_nr
)

SET @WerknemerID=@GebDat



END

==================================================================================

 

[Paul Shapiro]

I'm not sure, but I think the issue is that SQL Server takes dates delimited
with single quotes. To Access, that's a text string. Access delimits dates
with # instead.

Maybe this line would work correctly:
cmd.parameters.Append cmd.CreateParameter("@GebDat", adDate, adParamInput, ,
#2008-04-19 2:05:06 PM#)

or you could try using CDate("2008-04-19 2:05:06 PM") for the parameter
value.

Access presumably wants a date variable, and is trying to convert whatever
you give it. While SQL Server gets yyyy-mm-dd correct regardless of the
regional settings, you'll have to test in Access. The CDate() function I
gave above returned the correct date when run on a USA system.

 

[M. Bruil]

The weird thing is that when I pass the @GebDat as a Date, using a Date
parameter, and I try to return it with @WerknemerID (after changing the type
from int to date), I get the date value returned properly (I commented out
the insert into statement).

So the stored procedure accepts the parameter...