Select Distinct Records

[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 )

That return nothing even though Test has records for today....

Any clues???
Thanks!!

[Fumigator]

Why not just use the date() function?


SELECT *
FROM Test
WHERE date(TheDate) = CURDATE()

[terp_in_umcp]

Hey there....I tried that but it gives me an error:

'date' is not a recognized function name.

Somehow I want to strip the datetime value from the database and compare it with todays date...

[Fumigator]

Is that an ASP error or a MySQL error? Because date() certainly is a valid MySQL function:

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

[terp_in_umcp]

Probably its an ASP error...you think i should post this is the ASp forum??

[Fumigator]

I just noticed this:

DATE() is available as of MySQL 4.1.1.

If your version does not support date() then you should a. upgrade :D

You can also format the timestamp to compare against, assuming your version supports it...


SELECT *
FROM Test
WHERE date_format(TheDate, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')

[Fumigator]

We're tripping over each other's posts... heheh...

Probably its an ASP error...you think i should post this is the ASp forum??

Check your MySQL version first and determine if your version can do date().

[terp_in_umcp]

Ya ferm looks like it does support date() coz I use it all the time...let me try when you wrote...

[Daemonspyre]

This is totally an ASP error.

1) Add a space in you SQL statement between LIKE and your single quote.

2) &date()'" -- You are missing a second &


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


You will also run into problems because ASP date formats as 04/11/2007 while MySQL has a date format of 2007/04/11.

If you want to use NOW() or DATE() in ASP (no matter the version of MySQL), try CURDATE() in your MySQL statements. DATE() is only available in newer MySQLs, and it will help keep you from getting confused between ASP and MySQL functions.

HTH!

[terp_in_umcp]

Hey 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/11/sql_server_date.html

[Fumigator]

Ew.

You can compare a date stored in MySQL to the current date all within the MySQL query. Why use a variable from ASP at all? MySQL is very good at date manipulation and you should take advantage of that.

[terp_in_umcp]

Hmm thats true but Curdate() and format date both gave me an error:(

Anything to do with tbe version...if so how i can find the version??

[Daemonspyre]

I have to agree with Fumigator here. If you are using the ASP DATE() function to return today's date, use MySQL's CURDATE() instead.

You will run into a LOT less issues with MySQL v M$ date formats, and you will never have to use ASP's datepart() function to put the date into MySQL records.

As to your question with regards to .....WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Thedate))) = '"&date()&"'

CONVERT() provides a way to convert data between different character sets. (MySQL Manual, http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html)

Since ASP uses ASCII/UTF-8 and you are probably using LATIN1 on your MySQL tables, you are having to convert your date into a decimal format (FLOAT). From that decimal format, FLOOR() returns the largest integer value not greater than X, converted to a BIGINT. That BIGINT is then being converted back into an ASP date format.

It's easier to use DATEPART("yyyy",NOW()) & "-" & DATEPART("m",NOW()) & "-" & DATEPART("d",NOW()) than it is use CONVERT().

So... In retrospect, what's easier? Changing your SQL statement to use CURDATE() or use ASP and MySQL to do a lot of date conversion?

CURDATE() is easier on the server (less for ASP and MySQL to muddle through), and easier to code (FIND AND REPLACE are your friends. Use them wisely :) ).

CONVERT() is a whole lot of hassle for a not-so-accurate date conversion (and is really used for text-strings).

HTH!

[Fumigator]

PHP has a function mysql_get_server_info() (http://us2.php.net/manual/en/function.mysql-get-server-info.php), don't know if ASP has the equivelant. If your hosting company allows you to do command-line functions, you can find out on the command-line. Or just call em.

curdate() works with any version so if it's not working, it's your fault :D We can only be helpful if you post the code you used and the error you got.

[Daemonspyre]

Here's how you get the version of MySQL.

SHOW VARIABLES LIKE 'version';

If you are getting errors by using CURDATE(), try CURRENT_DATE or CURRENT_DATE(). They are all synonyms for each other.

[terp_in_umcp]

Thanks u guys for your input...

The newbie that I am , where do i Put SHOW VARIABLES LIKE 'version';

Definately not in my asp page...is it in the sql query analyzer or where?

[Daemonspyre]

Just a little more info for you:


mysql> SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE;
+------------+----------------+--------------+
| CURDATE() | CURRENT_DATE() | CURRENT_DATE |
+------------+----------------+--------------+
| 2007-04-11 | 2007-04-11 | 2007-04-11 |
+------------+----------------+--------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'version';
+---------------+----------------------+
| Variable_name | Value |
+---------------+----------------------+
| version | 5.0.24a-community-nt |
+---------------+----------------------+
1 row in set (0.00 sec)


To use DATE() in MySQL, you have to use:


mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2007-04-11 |
+-------------+
1 row in set (0.00 sec)

OR

mysql> SELECT DATE(CURRENT_TIMESTAMP());
+---------------------------+
| DATE(CURRENT_TIMESTAMP()) |
+---------------------------+
| 2007-04-11 |
+---------------------------+
1 row in set (0.00 sec)


If you use DATE() in ASP, you would get "04/11/2007" while NOW() would return "04/11/2007 13:40:20"

[Daemonspyre]

On you ASP page, you can use this:

<%
Set Conn = Server.CreateObjects("ADODB.Connection")
Conn.Open (your connection string)
'
Set rs = Server.CreateObject("ADODB.Recordset")
'
rs.open "SHOW VARIABLES LIKE 'version';", conn
'
for each fld in rs.fields
response.write(fld.Name & " - " & fld.Value & "<br>")
next

rs.close
Conn.close
%>


If you need more ASP help, then let's move this topic to the ASP forum under a new thread.

[terp_in_umcp]

Just back from lunch...hey guys thanks for tht info...truly helpful....I will keep posting more posts as and when I run into problems and as usual your feedback is higly valuable:))

Meanwhile I will try what you guys have suggested:)

[terp_in_umcp]

Hey there guys,

In the same query that I was using earlier...here are a few more problems i ran into....I want to be able to use Distinct and get rows with distinct Number. Also I want to be able to Order by Thedate. If i include Thedate in the select then it doesnt show distinct Number records.If I dont include Thedate in the Select, then it errors up...

mySQL = "Select Distinct(Number), Name, Thedate from History WHERE Thedate = '04/11/2007' ORDER BY TheDate desc"
Set rs= Con.Execute( mySQL )

Any better way to figure this out???
Thanks!!

[Daemonspyre]

Your error is probably because TheDate (if a DATE field) will never equal '04/11/2007'.

MySQL date format will force it to be '2007-04-11'.

Try:


mySQL = "Select Distinct(Number), Name, TheDate FROM History WHERE Thedate = CURDATE() ORDER BY TheDate DESC"
Set rs= Con.Execute( mySQL )


Can you post your CREATE TABLE syntax for me so that I can re-create your table and ergo, be able to help you better?

[guelphdad]

Daemonspyre, distinct is not a function, it will not only return distinct number but also name and date. it works on all columns selected.

So all three of the following rows will be returned:

5, 'George', '2007-07-22'
5, 'George', '2007-07-21'
5, 'George', '2007-07-23'

because at least one column in the row is different.

terp_in_umcp

if your dates are the way you are showing them then you are not storing them in date data type. See the article in my signature on how you can do an on the fly conversion using STR_TO_DATE, or better yet, do a one time conversion and store them in the proper data type.

As to your problem with your results, perhaps you can show some sample data and the resulting rows you want output and we can go from there.

[Daemonspyre]

Guelph, about the DISTINCT, I know.

my comment was not about the DISTINCT, it was about the date format.

He's using Classic ASP, not just MySQL, and Classic ASP has a different date format, which would screw up his SELECT.

[guelphdad]

Guelph, about the DISTINCT, I know.

This is misleading:
Select Distinct(Number), Name, TheDate

and suggests that distinct used there will only return rows with numbers not being duplicated.

As for the ASP, if the OP is not inserting their dates correctly into a valid date field then they can't use and DATE or TIME functions on that field. My suggestion was to read the article indicated to know how to change those dates around permanently.

[terp_in_umcp]

Hey guys....thanks for your feedback...I will certainly read the article...

However right now the dates are inserting fine...i used something that i posted earlier on(.....WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Thedate))) = '"&date()&"'....) and that seemed to insert the dates fine...Offcourse I know thats not the best way and I need to read your article and find a better way to do that...

But for now, to eliminate tht, I just hardcoded values and am trying to figure out the DISTINCT problem....

So with this query:
mySQL = "Select Distinct(Number), Name, Thedate from History WHERE Thedate = '04/11/2007' ORDER BY TheDate desc"
Set rs= Con.Execute( mySQL )

Result is:
Number Name

100 Name1
200 Name2
100 Name1
200 Name2

As you can see the Number is not Distinct mainly becoz Thedate is different for each record and its also taking tht into consideration....However I am trying to get unique Number records and ORDER BY TheDate...If I dont include TheDate in my Select then it gives error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified"

Ideally I think you can use only 1 condition when using DISTINCT right?

Any way out of this??
Thanks!!!

[guelphdad]

so for each number where there is more than one date which record do you want returned? the one with the newest date? If so you want the maximum group value for each group. in that case read this article (http://guelphdad.wefixtech.co.uk/sqlhelp/latestfromgroup.shtml).

[terp_in_umcp]

Hey there...The article is pretty neat although the dumbass that I am, its taking me forever to figure out:(

I think I am going around in circles...help me here pls...

So I need to pull Distinct Number records from the table....but i have to include Thedate field in my select coz thats what I am gonna order by...so maybe something like this:

Select Number, TheDate from Test WHERE
Thedate = '04/11/2007' and Number in (Select Number FROM Test where Thedate= '04/11/2007' and Thedate=Max(Thedate)) ORDER BY TheDate desc

What a mess...I dont know what i am doing:( Obviously the thing in red is wrong....

HELP!!!

[guelphdad]

1) show some sample rows from your table
2) show some rows from that sample that you are looking for in your output
3) you will not be able to order by your date column properly because you do not have a proper date type column

[Fumigator]

What guelphdad said.

Also, if you think about what you're trying to do, it doesn't make sense. You want to list the distinct numbers, but then you want to sort it by a date, but which date? If you have three thousand rows which all have 100 as the number, and those three thousand rows have three thousand different dates, how is your query supposed to know which date you want to use in the sort?

I just noticed you are trying out the column function max(), which you can use in your situation-- you just need to switch from using "distinct" to using "group by". I'll give you an example query here, but keep in mind THIS WON'T WORK until you correct your problems with your date field (which isn't a date type) that guelphdad has mentioned a few times already.


SELECT number, max(TheDate) as maxDate
FROM Test
GROUP BY Number
ORDER BY maxDate

[terp_in_umcp]

Hey u guys...I agree tht what I am trying to achieve is pretty complicated and with the kind of coding tht i am doing its too messy...

However I tried something that kinda worked...its not anything near perfect and just a quick fix...I still have no clue how to just get date from a datetime field and compare it with todays date in sql...I always did it in asp:(

<%

mySQL = "SELECT DISTINCT Number FROM Test WHERE CONVERT(DATETIME,FLOOR(CONVERT(FLOAT, Thedate))) = '"&date()&"'"
Set rs= Con.Execute( mySQL )

%>

<%

do while rs.eof<>true%>

<% mySQL = "SELECT * FROM Test WHERE CONVERT(DATETIME,FLOOR(CONVERT(FLOAT, Thedate))) = '"&date()&"' and Number='"&rs("Number")&"' ORDER By Thedate desc"
Set rs1= Con.Execute( mySQL )
%>


-----Displaying data------
<% =rs1("Number")%>
<%=rs1("Thedate")%>

<% rs.movenext
loop
%>

[Fumigator]

What I don't understand is why you'd bother sorting by TheDate when all of the TheDate values have to equal one value.

[terp_in_umcp]

Well I was trying to sort by Thedate coz Thedate is a datetime field with values like 4/12/2007 3:32:02 PM
4/12/2007 3:13:17 PM
4/12/2007 3:13:08 PM


So in a nutshell, I was trying to get just the date out of it, compare it with today's date, get the corresponding Number on the record and display the record...but only display records with distinct Number and sort tht list by Thedate....uff too much:))

[guelphdad]

Do you not understand that you can't sort by that field because it isn't a DATE type field?

Is there a reason you are not going to convert it to a DATE type and then be able to sort on that field?