|
Home > Archive > MS SQL Server > September 2005 > SET DATEFORMAT Not Working
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SET DATEFORMAT Not Working
|
|
| dearle via SQLMonster.com 2005-09-29, 1:23 pm |
| 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
| |
| Jerry Spivey 2005-09-29, 1:23 pm |
| 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
| |
| Joao Rego 2005-09-29, 1:23 pm |
| 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:
> 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...
>
>
>
| |
| Nik Marshall-Blank \(delete fcom for my email addr 2005-09-30, 3:23 am |
| 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...[color=darkred]
> 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:
>
| |
| dearle via SQLMonster.com 2005-09-30, 7:23 am |
| 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
| |
|
|
|
|
|