Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jan 2007
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get just the date from datetime field

    Hi there...

    I am trying to compare the date part of a datetime value field with today's date....

    Here's the sql:

    mySQL = "Select * from Test WHERE TheDate LIKE '"&date()"' ORDER BY TheDate"
    Set rs= Con.Execute( mySQL )

    As you can see, I dont know how to strip just the date from TheDate field and compare it with todays date...

    Right now the query returns nothing even though Test has records for today....

    I posted this in SQL forum but turns out its an ASP problem...

    Any clues???
    Thanks!!

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Terp --

    See my post in the MySQL forum.

    Basically - Your SQL has 2 errors:

    1) LIKE'" --> Needs to have a space in between LIKE and single quote.

    2) You are missing a second '&' in your statement.

    Code:
    mySQL = "SELECT * FROM Test WHERE TheDate LIKE '" & date() & "' ORDER BY TheDate"
    Set rs= Con.Execute( mySQL )

  • #3
    New Coder
    Join Date
    Jan 2007
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Daemon, Fumi....finally i got it...i tried everything you guys said, but looks like something was wrong with the way the dates are returned...

    I got it working right but dont understand how its done...so your further input with the explanation will be highly appreciated

    Here's how it worked:

    .....WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Thedate))) = '"&date()&"'

    Here's where i got it from:

    http://ewbi.blogs.com/develops/2004/...rver_date.html

  • #4
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That code basically removes the time part from the Date/Time field.

    You could also do it by using the style argument
    http://msdn2.microsoft.com/en-us/library/ms187928.aspx


    or by constructing your own date format using the inbuilt date/time functions, such as Day, Month and Year.


  •  

    Posting Permissions

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