How to pass DateTime to stored procedure from ASP

[techzone12]

How can I pass DateTime Paramter to stored procedure from within an ASP page? Can you give me an example - or maybe point me to a link where this is explained?

Thanks

 

[Sheco]

One thing to remember when using dates is that they may also have time... down to the millisecond.

So if you want to query find all the rows with a value on some specific date, you may need to check using a range between midnight and 11:59:59:999 PM

Also be aware that, in SQL Server, a timestamp is not a date.... it is rather a time offset that is guaranteed unique. So if you need a real date in your stored procedure use datetime or smalldatetime along with the GETDATE() function.

Also be aware that, if you plan to use the date from SQL server with the VB 6 "date" data type... and to use it in ad-hoc SQL statements ... just be aware that VB's date does not have milliseconds so when you go to convert it as a string then that value will be lost... this can be a serious problem if, for example, the datetime field is part of a table's compound primary key.

 

[DotNetGnat]

samples:

If using a concatenated string to execute the procedure, you should reformat 
the dates to ISO format (yyyymmdd) and concatenate them in as delimited 
strings: 

[blue]sSQL = "exec myproc '" & "20030101" & "', '" & "20030131" & "'" [/blue]


If using the procedure-as-connection-method technique, just pass them as 
normal arguments: 

[blue]cn.myproc cdate("01/01/2003"),cdate("01/31/2003")[/blue]

If opening a recordset: 
[blue]
set rs = server.createobject("adodb.recordset") 
cn.myproc cdate("01/01/2003"),cdate("01/31/2003"), rs 
[/blue]

-DNG

 

[Sheco]

Oh, I guess this might be obvious but the easy solution to the time part is to just not use it if you don't need it... So if all you really need is the day use the Date() function to get system date instead of Now() for system date+time.

Also be aware that the time on your web server will probably not be exactly the same as the time on your datbase server.

 

[techzone12]

Can you show me the stored procedure SQL code too?

 

[chmohan]

http://www.aspemporium.com/tutorials.aspx?tid=4

 

[Sheco]

[tt]
CREATE PROCEDURE sp_MyStoredProcedrue
@DateVar1 datetime,
@DateVar2 datetime,
@SomeString varchar(20)
AS
SET NOCOUNT ON

<Put the rest of your sp here>
[/tt]