www.webdeveloper.com
Results 1 to 5 of 5

Thread: How do you compare date values with MS SQL Server

  1. #1
    Join Date
    Jul 2004
    Posts
    61

    How do you compare date values with MS SQL Server

    I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?

    Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.

    Here is my code:
    dim conpubs as sqlconnection
    dim cmdSelectAuthors as sqlcommand
    dim dtrAuthors As sqlreader

    conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
    conpubs.open()
    cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
    dtrAuthors = cmdSelectAuthors.ExecuteReader()
    Response.Write("<table><tr><td>")

    While dtrAuthors.Read()
    Response.Write(dtrAuthors("title") & "<br>")
    End While

    Response.Write("</td></tr></table>")

    dtrAuthors.close()
    conPubs.close()

  2. #2
    Join Date
    Nov 2002
    Location
    Auburn, AL
    Posts
    9,224
    http://msdn.microsoft.com/library/de...iFunctions.asp
    That may be useful. But you are going to just be comparing the dates, just use the now function for current and then say if they are = then do something. That link is for vbscript, not vb.net... Ill hunt around for the vb.net one, but those should be the same.
    http://msdn.microsoft.com/library/de...nctionsVBA.asp here is visual basic
    Last edited by PeOfEo; 08-02-2004 at 01:04 PM.

  3. #3
    Join Date
    Nov 2003
    Posts
    655
    The problem is that you're comparing a date in a string against a datetime datatype, which contains date AND time.

    Try this:

    ... WHERE DATEDIFF(day, date_created, '" & y & "') = 0)"

  4. #4
    Join Date
    Nov 2003
    Posts
    655
    Another alternative is to change the default for "date_created" from

    GETDATE()

    to

    CONVERT(varchar(10), Get_Date(), 101)

    The convert (followed by an implicit cast) will give you the date with no time, which will match correctly to the strings you are using right now. You would have to update all the current data in the database for it to work on both the old as well as the new.

    ... SET date_created = CONVERT(varchar(10), date_created, 101)


    If you do go with this, you should consider using smalldatetime instead of datetime, as it's half the size and will go further into the future than datetime, though it doesn't go back before 1900 (which probably isn't a concern to your application, but you'd know better than I).

  5. #5
    Join Date
    Mar 2010
    Posts
    2
    for compare date only in date time field in sql do not use either cast or convert. They are not efficient and not recommended. So, here is the complete solution which works perfect.
    Compare date only in date time field
    Let me know, if any issues still...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles