Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    2

    Unanswered: Error converting data type nvarchar to datetime

    Hi, I am new to SQL and was wondering if anyone could help me with a procedure i am trying to create.



    ALTER PROCEDURE [dbo].[spCountRegistrations]

    @Date datetime

    AS

    DECLARE @SQL varchar(1000)

    SET @SQL = 'SELECT COUNT(*)
    FROM aspnet_Membership
    WHERE 1 = 1'

    IF @Date <> '' SET @SQL = @SQL + ' AND CreateDate = ' + @Date + ''

    EXEC(@SQL)



    When I execute this, and pass @Date as NULL then it works correctly. However if i pass a date to @Date, I get the following error:

    Msg 8114, Level 16, State 5, Procedure spCountRegistrations, Line 0
    Error converting data type nvarchar to datetime.

    I'm pretty sure its something to do with the format of the date, but cannot find any suitable solutions.

    Any help would be greatly appreciated.

    Many Thanks

    Matt

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Matt

    I'm afraid there is lots wrong in your post.
    Code:
    ALTER PROCEDURE [dbo].[spCountRegistrations]
    
        @Date DATETIME
    
    AS
    
    IF @Date IS NULL
    BEGIN
        SELECT  COUNT(*)
        FROM    aspnet_Membership
    END
    ELSE
    BEGIN
        SELECT  COUNT(*)
        FROM    aspnet_Membership
        WHERE  CreateDate = @Date
    END
    GO
    Some stuff to google:
    sql injection
    sql concatenating NULL
    sql server optional parameters
    sql server data type conversion

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, these will do for searches 1) & 3)

    Dynamic Search Conditions in T-SQL
    The curse and blessings of dynamic SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •