errors inserting dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

errors inserting dates

Author

Topic

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-10-26 : 05:34:56

When i try to insert into a datetime field i keep getting this error.

Msg 242, Level 16, State 3, Procedure Sproc_01220_Tour_PendingPayment, Line 27
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

(1 row(s) affected)

i am passing to the date parameters the date in this format dd/mm/yyyy

Any ideas

ALTER PROCEDURE [dbo].[Sproc_01220_Tour_PendingPayment]

@MemberId int =Null,
@TourStartDate DateTime =Null,
@Initial_TourQty int =Null,
@PaidUntill DateTime =Null

AS
BEGIN--Overall
SET NOCOUNT ON;

BEGIN--Insert

INSERT INTO Tbl_01040_Tour_Join_PendingPayment
(Pend_MemberId,
Pend_TourStartDate,
Pend_Initial_TourQty,
Pend_PaidUntill)

VALUES (@MemberId,
@TourStartDate,
@Initial_TourQty,
@PaidUntill)

END--Insert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 05:36:40

Try to pass the parameter as yyyymmdd instead.

Peter Larsson
Helsingborg, Sweden

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-10-26 : 06:40:28

Or else specify the date format in the store procedure

Like

ALTER PROCEDURE [dbo].[Sproc_01220_Tour_PendingPayment]

@MemberId int =Null,
@TourStartDate DateTime =Null,
@Initial_TourQty int =Null,
@PaidUntill DateTime =Null

AS

Set nocount on

Set Dateformat DMY

INSERT INTO Tbl_01040_Tour_Join_PendingPayment
(Pend_MemberId,
Pend_TourStartDate,
Pend_Initial_TourQty,
Pend_PaidUntill)

VALUES (@MemberId,
@TourStartDate,
@Initial_TourQty,
@PaidUntill)

Set nocount off

Don't sit back because of failure. It will come back to check if you still available. -- Binu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 06:55:54

I don't think that is an issue since the parameter, internally in SP, is datetime. I think it is the first time when parameter is assigned that the error occurs.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 08:37:03

quote:
Originally posted by sachinsamuel

Set Dateformat DMY

This won't help.

As Peter suggested, pass in the date as universal format YYYYMMDD

or

change the input parameter as varchar and convert to datetime internally in the SP


ALTER PROCEDURE [dbo].[Sproc_01220_Tour_PendingPayment]
@MemberId int =Null,
@TourStartDateChar varchar(10) = Null,

As
Begin
    declare @TourStartDate datetime

    select @TourStartDate  = convert(datetime, @TourStartDateChar, 103)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-26 : 09:20:48

quote:
Originally posted by khtan
...
or

change the input parameter as varchar and convert to datetime internally in the SP...

Blasphemy!!!

Leave it as a datetime parameter, otherwise you have to put a whole lot more checking in the strored procedure and then convert it to datetime.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 09:22:40

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by khtan
...
or

change the input parameter as varchar and convert to datetime internally in the SP...

Blasphemy!!!

Leave it as a datetime parameter, otherwise you have to put a whole lot more checking in the strored procedure and then convert it to datetime.

CODO ERGO SUM

Yes it is

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-26 : 09:37:48

http://sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources