stored procedure not recognizing date

Posted on 2006-04-11
Last Modified: 2010-04-16
CmdDups.Parameters.Add("@LName", SqlDbType.VarChar, 40);
CmdDups.Parameters.Add("@DOB", SqlDbType.SmallDateTime);
CmdDups.Parameters.Add("@Address", SqlDbType.VarChar, 50);

CmdDups.Parameters["@LName"].Value = LName.Text;
CmdDups.Parameters["@DOB"].Value = TxDOB.Text;
CmdDups.Parameters["@Address"].Value = Address.Text;

I have the above stored procedure.  After updates I pass the values to check if the values exist in another table.  I have a record that I have entered that is in the other table.  I am having problems with the DOB field.  When I type "01/12/1946", the parameter runs okay and if identifies that the entry is already in another table.  If I pass the text value of a textbox or datetimepicker the procedure executes fine, but it does not identify that the record is already in the other table.

Please help.
Question by:yanci1179
    LVL 6

    Accepted Solution

    What if you did:

    CmdDups.Parameters["@DOB"].Value = Convert.ToDateTime(TxDOB.Text);

    does that do anything?

    Author Comment

    no, it just goes right over it.  I don't get any errors but it does not identify a match with the date that is in the database.  It only makes a match if I actually type "01/12/1946"

    any other suggestions?  I have no clue what is wrong!!

    Author Comment

    this is my stored procedure:

    CREATE proc dbo.usp_SEDupCk
    (@LName varchar(40),
    @DOB smalldatetime,
    @Address varchar(50))
    SELECT       SE.DtSched, SE.MSI, SE.HH_ID,SE.Barcode , SE.LName, SE.MName, SE.Fname, SE.DOB,
          HH.Address, HH.City, HH.Zip,HH.HmNum, HH.OtherNum
          FROM       tblHH HH, tblSE SE
                (SE.HH_ID = HH.HH_ID AND (lname LIKE '%' + @lname + '%' AND DOB = @dob))
                OR (address LIKE '%' + @address + '%' AND SE.HH_ID = HH.HH_ID)
    LVL 7

    Assisted Solution

    Comparing a datetime in SQL is always a risky business even if you use the smalldatetime.
    The trouble is that the datetime is not exactly the same (differs in hours and minutes - no seconds with smalldatetime).

    1) In code:
    CmdDups.Parameters["@DOB"].Value = DateTime.ParseExact(TxDOB.Text, "dd/MM/yyyy", new System.Globalisation.DateTimeFormatInfo()).Today;
    The Today strips all kind of hour and minute information from the Datetime.

    2) In the Stored Proc:
    Change: ... DOB = @dob ...
    Into: ... DATEDIFF(d, DOB, @dob) = 0 ...

    Hope this helps,
    LVL 15

    Expert Comment

    change the SP to

    AND DOB = @dob)


    convert(datetime,convert(char(10),<DOB >,101)=@dob
    LVL 15

    Assisted Solution

    i mean convert(datetime,convert(char(10),DOB,101)=@dob

    if that doesnt work try this


    Author Comment

    Thanks for the solutions, I'm going to try each one, one at a time.


    I tried:

    DateTime.ParseExact(TxDOB.Text, "dd/MM/yyyy", new System.Globalization.DateTimeFormatInfo()).Today;

    and I got the following error:

    Static member 'System.DateTime.Today.get' cannot be accessed with an instance reference; qualify it with a type name instead      

    Author Comment

    Well, I hate to say this but I tried my original code today and it worked!!??

    I'm not sure what happened, I guess I'm going to have to test it a few more time to make sure that it is working.  

    thanks again for the help.
    LVL 7

    Expert Comment


    DateTime.ParseExact(TxDOB.Text, "dd/MM/yyyy", new System.Globalization.DateTimeFormatInfo()).Date;

    Must have been half sleeping when I typed this...

    My appologies,

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now