sql server 2000 i dont know "datetime" parameter's length 3

[pssheba]

Very shamefull indeed but i couldnt find it anywhere...
My stored procedure's parameter are of "datetime" type and it starts with:

CREATE PROCEDURE myProc @start_d datetime(8), @end_d datetime(8)

The error i get is:

Column or parameter #1: Cannot specify a column width on data type datetime.

I guess the length is wrong.
Someone saves me from that ?

 

[ESquared]

Don't put any parentheses or a number, it's datetime, period.

CREATE PROCEDURE myProc @startdate datetime, @enddate datetime

Also may I suggest startdate/enddate instead of start_d end_d... typing the underscore is such a pain in the neck. Some future developer who inherits your project will thank you for it.

If you want the 4-byte date time data type, it's smalldatetime.

 

[pssheba]

Thank you ESguared.
If i want to insert value to that datetime parameter, how do i do that:

exec myProc '12-11-2006'

or

exec myProc convert(varchar,12-11-2006,110)

could you help me with that please?

 

[gmmastros]

When specifying dates, you should use the unseperated ISO format, which is YYYYMMDD.

Also, since your procedure has 2 parameters, you need to specify both of them, like this...

Exec myProc '20061211', '20061212'

Take a look at this thread thread183-1240616 for reasons why.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

And please understand that DATETIME datatype is date AND time. Since you are not entering a time, SQL Server will automagically add 00:00:00.000 to the date.

Why is this important to know? You are setting a start and end date, so it's likely you are using them in a WHERE clause. Let's say your dates are:

start_d = 12/18/2006
end_d = 12/19/2006

With a WHERE clause like this:
WHERE mydate >= @start_d
AND mydate <= @end_d

Will you get the value of 12/19/2006 01:00??? No, you won't. That value is GREATER than 12/19/2006 00:00:00.000.

For more information on working with DATETIME, check out the FAQs in this forum. There is a section specifically for DATETIME tips & tricks.

-SQLBill

Posting advice: FAQ481-4875

 

[pssheba]

Thank you very much.
I'll be using your instruction.