SQL Server Performance

Datetime as SP parameter

Discussion in 'General Developer Questions' started by chilisauce, Jun 10, 2003.

  1. chilisauce New Member

    What is the best way to use the datetime data type in a stored procedure as a parameter?

    I have created a stored procedure which exports data to an excel file. Therefore, I would like to pass in a date from an application to further limit the amount of rows returned. I have no problem with strings as parameters but I cannot seem to wrap my brain around the datetime issue. DAMN TEXAS HEAT!!!!

    Thanks in advance!!

    -chili
  2. bambola New Member

    The best way would be to use yyyy-mm-dd. it's an absolute format and you cannot go wrong with that.

    Bambola.
  3. chilisauce New Member

    Thanks for the response.

    I do understand that. Therefore I need to clarify. I need to accept a datetime parameter. Then concatonate that parameter into a query string which will eventually get execute. Simplified scenerio below:

    create proc blah
    @date1 datetime,
    @date2 datetime
    As

    declare @query varchar(8000)

    @query = 'select column1, column2 '
    @query = @query + 'from db..randomtable '
    @query = @query + 'where datetimecreated between'' + @date1 + '' and '' + @date2 ''

    exec(@query)

    Remember I am populating an excel spreadsheet with the results. Therefore I am working with COM objects to accomplish this task. This is the reason for the query string.

    Appreciate your help,

    -chili
  4. Logicalman New Member


    Chili,

    Try the following code. It can be called using the following syntax:

    exec sp_test_Dates '01/01/2001', '01/01/2003'

    (Note the use of single quotes, double quotes will work just as well)

    It accepts the dates as Strings, and then converts them for use as DateTime data types. If you didn't need to use the String variable @query, then it's all so much easier, you can simply declare the params as Datetime data types and place them directly into the code to execute immediately, without the need to create a String to execute first.

    Hope this helps, anyway,

    Logicalman


    ***** CODE STARTS HERE *****

    --#Get two date params and return a RS based on that criteria
    CREATE PROCEDURE [dbo].[sp_test_Dates]

    --# Starting Date
    @@Date1 varchar (50) =0,
    --# Ending Date
    @@Date2 varchar (50) =0

    AS
    --# Convert the parameters into 'true' Strings
    set @@Date1 = char(39) + @@Date1 + char(39)
    set @@Date2 = char(39) + @@Date2 + char(39)

    --# Declare the TSQL String variable to hold the String to execute
    declare @query varchar(8000)

    --# Create the String to execute
    SET @query = 'select column1, column2 '
    SET @query = @query + 'from db..randomtable '
    SET @query = @query + 'WHERE (datetimecreated BETWEEN CONVERT(DATETIME, '+ @@Date1 +', 102) AND CONVERT(DATETIME, '+ @@Date2 +', 102)) '

    --# Debug Print it
    PRINT @query

    --# Now execute the thing
    exec(@query)
    GO

    ***** CODE ENDS HERE *****

Share This Page