set dateformat to dd-mm-yyyy problem

i write procedure which receives date in format dd-mm-yyyy
mm can be 1 or 2 digit similar with date e.g.
1-1-2006 ,12-28-2005
how i set the dateformat
i write set dateformat dmy it sometimes give error
Thanks

quote:Originally posted by FrankKalis

What error do you get?

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Error is
Error converting data type varchar to datetime
procedure sometimes work and sometimes not work

how do you conver to datetime datatype. Can you post your code ?


KH

When feeding dates in strings, use the 'yyyy-mm-dd' format only - will always be interpreted correctly by SQL Server.

The only other accepted format is the Windows date format on the server on which SQL Server is running. This is hard to see from the outside, so don't bother and just use 'yyyy-mm-dd'.

quote:Originally posted by khtan

how do you conver to datetime datatype. Can you post your code ?


KH

My code is
...................
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER procedure SPScheduleVehicle
@vehicle_id nvarchar(30),
@trip_id uniqueidentifier,
@from_date datetime,
@to_date datetime
As
Begin
set dateformat dmy
if not exists (select vehicle_id from schedule_details
where vehicle_id=@vehicle_id)
Begin
insert into schedule_details(trip_id,vehicle_id,from_date,to_date)
values(@trip_id,@vehicle_id,@from_date,@to_date)
End
Else
Begin
update schedule_details
set trip_id=@trip_id,
from_date=@from_date,
to_date=@to_date
where vehicle_id=@vehicle_id
End
End


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

.................................
if i pass date as 1-1-2006 it works fine
but when i give 30-12-2006 it gives error

use
select convert(datetime, '30-12-2006', 103)

your problem is the input parameter @from_date & @to_date is datetime format

so when you called the sp
exec SPScheduleVehicle 'whatever', 1, '30-12-2006'
it will treat the 30-12-2006 in MM-DD-YYYY format. Which means the set dateformat dmy has no effect at all.

Pass the datetime in YYYYMMDD format.
exec SPScheduleVehicle 'whatever', 1, '20061230'





KH

"if i pass date as 1-1-2006 it works fine
but when i give 30-12-2006 it gives error"
The convertion error is not within the SP but when converting the datetime in string to datetime datatype.


KH

Thanks everyone for taking time
When i change datatype of input parameter from datetime to nvarchar
it not gives any error