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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Stored Procedure parameter

Author  Topic 

ilimax
Posting Yak Master

164 Posts

Posted - 2005-10-17 : 13:22:11
Can I say something like this

CREATE PROCEDURE App_POSItems
@date_danas datetime=(convert(varchar,getdate,101))
AS
SET NOCOUNT ON


Actually, what I want is assigning date value to parameter inside of procedure. I want to assign value of today date in format of 10/17/2005.

Does anybody have idea how I can make this.

Thanks,

Ilimax

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-17 : 13:52:00
if the parameter's datatype is datetime then assigning the value from convert(varchar,getdate(),101) is a waste of time because sql server will just convert it back to datetime.

If you want to use a varchar variable then make the variables datatype varchar.

Typically, the database and the stored procedure will accept, use, and return datetimes and the presentation layer will present the dates in whatever format you want.

Finally, you wont be able to use an expression as a default value for an SP's input parameter. That will generate an error.

Be One with the Optimizer
TG
Go to Top of Page

ilimax
Posting Yak Master

164 Posts

Posted - 2005-10-17 : 14:45:09
You want to tell me that whatever value I send to Stored Procedure, Value will be converted into datetime if I assigne variable as datetime ...

For example this is my code from ADO ...I am sending format 10/17/2005 ...

Set prm_one = myPCmd.CreateParameter("@date_from", adDBTimeStamp, adParamInput, 8, fdate)
myPCmd.Parameters.Append prm_one


This is my procedure ... so value will be transform into date-time value 10/17/2005 12:00:00 PM

CREATE PROCEDURE App_POSItems
@date_danas datetime
AS
SET NOCOUNT ON
.....


So in that case I have to assign variable into Varchar, because my value in table is stored in format 10/17/2005


Am I right???









Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-17 : 15:41:53
What is the datatype of the column in your table? If it's datetime then the values are stored as two 4 byte integers, they are not stored as formatted dates.
quote:
You want to tell me that whatever value I send to Stored Procedure, Value will be converted into datetime if I assigne variable as datetime ...

Yes, see for yourself:
declare @d datetime

set @d = convert(varchar, getdate(), 101)
select @d [@d], convert(varchar, getdate(), 101) [varchar]

set @d = convert(varchar, getdate(), 112)
select @d [@d], convert(varchar, getdate(), 112) [varchar]


Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-17 : 15:45:36
What is the problem you're trying to solve? There is probably a simple solution.

Be One with the Optimizer
TG
Go to Top of Page

ilimax
Posting Yak Master

164 Posts

Posted - 2005-10-17 : 16:10:40
I never think how they are stored but I know how data looks in that colulmn. It's look exactly like this 10/17/2005. However, thanks for lesson.

Yes, datatype of columns is datetime but I set default value

convert(varchar, getdate(), 101)

I am trying to send one date parameter to stored procedure from my VB6 application.

That parameter will be always today date.

If there is option, I assign that value to variable in stored procedure, that would be fine too...

I want to pull data by date column .... date column should be <=today date.

Thanks

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-17 : 17:51:01
Is this the type of logic you're looking for?

CREATE PROCEDURE App_POSItems
@date_danas datetime = null --you can default it to null so that
--if no date was passed then use today
AS

SET NOCOUNT ON

--if no date was passed in then use today's date (at 00:00:00)
if @date_danas is null
set @date_danas = dateAdd(day, datediff(day,0,getdate()), 0)

--your select statement
select <columnList>
from <tables>

--for all dates equal or prior to today at 11:59:59.999 PM
where date_danas < dateAdd(day, datediff(day,0,@date_danas)+1, 0)

--for all datetimes during today (just today)
where dateAdd(day, datediff(day, 0, date_danas), 0) = @date_danas


Be One with the Optimizer
TG
Go to Top of Page

ilimax
Posting Yak Master

164 Posts

Posted - 2005-10-18 : 09:26:41
I see man there is my lack of knowledge of T-SQL ... I have to read more about sql server ...

TG, tell me what do you think about this ...looks like that works ...In this case I do not need to send parameter ...(what I want..)

CREATE PROCEDURE App_POSItems
--@date_danas datetime
AS
SET NOCOUNT ON
DECLARE @date_danas datetime
SET @date_danas = convert(varchar,getdate(),101)
INSERT INTO arhPOSInvoiceItems_2006 ( [Invoice Number] ....




However, thanks for replays ....

Ilimax

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-18 : 17:38:00
>>I see man there is my lack of knowledge of T-SQL ... I have to read more about sql server ...
We all do

Yes, if you will always use today's date the you don't need the input parameter, you can just use a local variable. For your insert statement you don't even need a parameter you can just use an expression. ie:

insert arhPOSInvoiceItems_2006 (Invoice_date, <otherColumns>)
values (getdate(), <otherColumnValues>)

Are you sure you want to lose the precision of the time component in your dates? You can always ignore the times with the techniques above, but you can never use the added precision if you aren't saving them.


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -