SET DATEFORMAT DMY does not working inside SP

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

SET DATEFORMAT DMY does not working inside SP

Author

Topic

omrs2002
Starting Member

5 Posts

Posted - 2010-08-30 : 07:38:39

why SET DATEFORMAT DMY does not working inside SP

Ex:
ALTER PROCEDURE [dbo].[Tasks_Insert]
@TskName nvarchar(100),
@OwnerEmpCode varchar(50),
@AssignedEmpCode varchar(50),
@TskSeverity varchar(50),
@TsStatID bigint,
@TskExpire datetime,
@TskDesc nvarchar(1000),
@PrjID bigint
-----------------------------------------------------------------------------------------------------------
AS
BEGIN

SET DATEFORMAT DMY ;
--SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Insert into Tasks Table
INSERT INTO dbo.Tasks
(TskID,
TskName,
EmpCode,
TskSeverity,
TsStatID,
TskExpire,
TskDesc,
PrjID
)
VALUES (@MaxTskID,
@TskName,
@OwnerEmpCode,
@TskSeverity,
@TsStatID,
@TskExpire,
@TskDesc,
@PrjID
)

END

can some one helps?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 07:40:47

Why do you think you need to set DATEFORMAT?
Your variable @TskExpire is already datetime.

Do you get any errors?

N 56°04'39.26"
E 12°55'05.63"

omrs2002
Starting Member

5 Posts

Posted - 2010-08-30 : 08:01:53

quote:
Originally posted by Peso

Why do you think you need to set DATEFORMAT?
Your variable @TskExpire is already datetime.

Do you get any errors?

N 56°04'39.26"
E 12°55'05.63"

Yes i get this error :
Error converting data type varchar to datetime

my system send the date in formate dd/MM/yyyy??

when i use the "SET DATEFORMAT DMY"
before SP execution it is working...
Like This:

SET DATEFORMAT DMY
EXEC Tasks_Insert
@TskName = 'ERER2', -- nvarchar(100)
@OwnerEmpCode = 'ADELSAMOUR', -- varchar(50)
@AssignedEmpCode = 'BASSAMSAFADI', -- varchar(50)
@TskSeverity = 'MEDIUM', -- varchar(50)
@TsStatID = 3, -- bigint
@TskExpire = '30-12-2011', -- datetime
@TskDesc = 'ff', -- nvarchar(1000)
@PrjID = 30 -- bigint

but inside the sp it does not working...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 08:10:11

Just a thought. When you already are hardwiring your date,
try using standard ISO datetime format and see what happens.

EXEC Tasks_Insert
@TskName = 'ERER2', -- nvarchar(100)
@OwnerEmpCode = 'ADELSAMOUR', -- varchar(50)
@AssignedEmpCode = 'BASSAMSAFADI', -- varchar(50)
@TskSeverity = 'MEDIUM', -- varchar(50)
@TsStatID = 3, -- bigint
@TskExpire = '20111230', -- datetime in standard ISO datetime format YYYYMMDD
@TskDesc = 'ff', -- nvarchar(1000)
@PrjID = 30 -- bigint

N 56°04'39.26"
E 12°55'05.63"

omrs2002
Starting Member

5 Posts

Posted - 2010-08-30 : 08:18:20

it is working ..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-31 : 06:02:18

Make sure to read this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-31 : 06:29:24

Madhi, it's not a column. It's parameter for a SP call.

N 56°04'39.26"
E 12°55'05.63"

omrs2002
Starting Member

5 Posts

Posted - 2010-08-31 : 06:37:20

Hi Peso..
it is working when i make the insertion manual ...
like this :
EXEC Tasks_Insert
@TskName = 'ERER2', -- nvarchar(100)
@OwnerEmpCode = 'ADELSAMOUR', -- varchar(50)
@AssignedEmpCode = 'BASSAMSAFADI', -- varchar(50)
@TskSeverity = 'MEDIUM', -- varchar(50)
@TsStatID = 3, -- bigint
@TskExpire = '20111230', -- datetime in standard ISO datetime format YYYYMMDD
@TskDesc = 'ff', -- nvarchar(1000)
@PrjID = 30 -- bigint
---------------------------------------

But the parameter "TskExpire" is comes as Datetime object

how can i insert it as standard ISO datetime format YYYYMMDD ???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-31 : 07:03:13

Can't tell. I don't know how you are calling the procedure.

N 56°04'39.26"
E 12°55'05.63"

omrs2002
Starting Member

5 Posts

Posted - 2010-08-31 : 07:28:00

I Call it from dot net web site

cmd.Parameters.AddWithvalue("@TskExpire",DateTime.Today);

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-31 : 09:44:52

quote:
Originally posted by omrs2002

I Call it from dot net web site

cmd.Parameters.AddWithvalue("@TskExpire",DateTime.Today);

It should be something like

cmd.Parameters.AddWithvalue("@TskExpire",tostring(DateTime.Today,"YYYYMMDD HH:MM:SS"));

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-31 : 09:46:20

quote:
Originally posted by Peso

Madhi, it's not a column. It's parameter for a SP call.

N 56°04'39.26"
E 12°55'05.63"

Yes. In that post, I also had given points about usage of dateformat and usage of unambiguous date formats

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources