SQL Server Performance Forum – Threads Archive

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

---

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

---

SET DATEFORMAT dmy
SELECT CAST(‘1-1-2006’ AS DATETIME)
——————————————————
2006-01-01 00:00:00.000 (1 row(s) affected) works as it should. So I guess the procedure gets invalid data passed such as
SET DATEFORMAT dmy
SELECT CAST(‘1-13-2006’ AS DATETIME)
Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
—
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)

---

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’.

---

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 [email protected]_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 [email protected]_id,
[email protected]_date,
[email protected]_date
where [email protected]_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

---

]]>