asked on

Save a Date (dd/mm/yy)

I have the following storedprocedure in a sql express database

ALTER PROCEDURE [dbo].[FillRunLog]
      -- Add the parameters for the stored procedure here
      @RunNo int = 0,
      @RunDate datetime
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

-- Insert statements for procedure here
      insert into RunLog(RunNo,Rundate)
      SELECT @RunNo,@RunDate)
END

i want to call this SP from code using a Command

Cmd.CommandText = "FillRunLog " & 5 & ", '" & Now & "'"
Cmd.ExecuteNonQuery()

this should fill 5 into RunNo and current date and time into RunDate

now has the valuse #2/21/2006 12:50:43 PM# which is the 21st of Feb. Which is correct.
I am pretty sure I am getting an error because it is trying to save this as the 2nd day of the 21st month which is american format.

How can get it to save the way I want.

ASKER CERTIFIED SOLUTION

DotNetLover_Baan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jerete

Why not use the date in the stored procedure?

ALTER PROCEDURE [dbo].[FillRunLog]
-- Add the parameters for the stored procedure here
@RunNo int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into RunLog(RunNo,Rundate)
SELECT @RunNo,GETDATE())
END

Eamon

ASKER

jerete i am doing that for the moment but that will not let me fill any date i want.
Baan I will try your method

Bob Learned

Save it as Y-M-D, and you won't have any problems, as this format is not ambiguous.

Bob

Eamon

ASKER

no Bob I want to save it as 22/02/2006 15:17

Bob Learned

You save it anyway way you want, because it doesn't matter what format you save it in.

But, remember that SQL Server expects American format dates, and has trouble with other formats, but doesn't have a problem with Y-M-D. Also, remember that what you get back from the database is not Y-M-D, and can be formatted to whatever format that you wish to display in.

Bob

Eamon

ASKER

but do I have to format what I am sending to the stored procedure