Get just the date from datetime field

[terp_in_umcp]

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!!

[Daemonspyre]

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.


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

[terp_in_umcp]

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

[degsy]

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.