SET DATEFORMAT Not Working

I cannot get the following stored procedure to accept dates in the format
dd/mm/yyyy.

alter proc usp_test
 @FirstName	varchar(3
0),
 @DateOfBirth	datetim
e
AS
set dateformat dmy

INSERT INTO 	tbl_test (
Name,
DateOfBirth)
Values
( @FirstName, @DateOfBirth)
GO

exec usp_test 'Dan', '16/09/2005'
I am passing it a date such as '16/09/2005'.  Surely by using the Set
DATEFORMAT dmy this should work?
If I run the set dateformat dmy command in query analyser and then execute
the sp it works.  Any ideas??

Thanks

Dearle,

Try removing the SET DATEFORMAT dmy out of the proc and issuing it prior to
the EXEC.

HTH

Jerry
"dearle via webservertalk.com" <u14413@uwe> wrote in message
 news:5518d051f3812@u
we...
>I cannot get the following stored procedure to accept dates in the format
> dd/mm/yyyy.
>
> alter proc usp_test
> @FirstName varchar(30),
> @DateOfBirth datetime
> AS
> set dateformat dmy
>
> INSERT INTO tbl_test (
> Name,
> DateOfBirth)
> Values
> ( @FirstName, @DateOfBirth)
> GO
>
> exec usp_test 'Dan', '16/09/2005'
> I am passing it a date such as '16/09/2005'.  Surely by using the Set
> DATEFORMAT dmy this should work?
> If I run the set dateformat dmy command in query analyser and then execute
> the sp it works.  Any ideas??
>
> Thanks

Hi,

Jerry Spivey is wright: "remove the SET DATEFORMAT dmy out of the proc"

I think you are calling the sp from an application... if this is the case
after you create the connection to the data base, you need to execute a
commando to the SQL "SET DATEFORMAT 'dmy'" from this point forward any sp yo
u
call from your program will have da date in the format 'dmy'

Joao

"Jerry Spivey" wrote:

> Dearle,
>
> Try removing the SET DATEFORMAT dmy out of the proc and issuing it prior t
o
> the EXEC.
>
> HTH
>
> Jerry
> "dearle via webservertalk.com" <u14413@uwe> wrote in message
>  news:5518d051f3812@u
we... 
>
>
>

Previous posts are correct and here's why.

SETDATEFORMAT determines how to interpret character dates into a datetime
variable. This has already happened before the sproc is called and will
therefore have no effect on an already converted datetime value which is
being passed.

--
Nik Marshall-Blank MCSD/MCDBA

"Joao Rego" < JoaoRego@discussions
.microsoft.com> wrote in message
news:1E52552C-A519-4F3A-A911- 606FE5E589A2@microso
ft.com...
> Hi,
>
> Jerry Spivey is wright: "remove the SET DATEFORMAT dmy out of the proc"
>
> I think you are calling the sp from an application... if this is the case
> after you create the connection to the data base, you need to execute a
> commando to the SQL "SET DATEFORMAT 'dmy'" from this point forward any sp
> you
> call from your program will have da date in the format 'dmy'
>
> Joao
>
> "Jerry Spivey" wrote:
> 

Thanks for your help.  What determines the original dateformat?  Is there a
wao of changing this?


Jerry Spivey  wrote:[color=darkred
]
>Dearle,
>
>Try removing the SET DATEFORMAT dmy out of the proc and issuing it prior to
>the EXEC.
>
>HTH
>
>Jerry 
>[quoted text clipped - 19 lines] 


--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200509/1

The language of the login determines the dateformat of the connection. See s
p_helplanguage. Also, an
explicit SET DATEFORMAT or SET LANGUAGE will override the login's default. A
lways pass the datetime
in a safe format and you don't have to worry about this. Or, if you code ADO
 etc, define parameter
objects for each parameter and connect to a date variable in the host langua
ge and the API will
handle this for you. See http://www.karaszi.com/SQLServer/info_datetime.asp 
for more information.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"dearle via webservertalk.com" <u14413@uwe> wrote in message  news:552127b0dca5d@u
we...[colo
r=darkred]
> Thanks for your help.  What determines the original dateformat?  Is there 
a
> wao of changing this?
>
>
> Jerry Spivey wrote: 
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200509/1[/color]