What's wrong with this MySQL SELECT statement?

BigToque · 02-10-2005, 04:32 AM

I have a table called "schedule" and that table has a field called "year". "year" is a float value.

All records contain one of these entries:

2004.11
2004.12
2004.13
2004.21
2004.22
2004.23
2004.31
2004.32
2004.33

This is my statement:

SELECT * FROM `schedule` WHERE year = 2004.32

The query runs and is supposed to select all records from the 2004.32 season, but it doesn't select any data.

hemebond · 02-10-2005, 05:14 AM

Change the column from FLOAT to DECIMAL. Direct comparisons seem to work in them.

Code:

DECIMAL(4,2)

Horus Kol · 02-10-2005, 10:45 AM

just out of interest - for storing date information, why not use a timestamp?

rayd · 02-10-2005, 05:47 PM

Seems like perhaps you need to store date of the year instead of month and date? If so, a timestamp might still be better, like what Horus said, then you can use : mysql> SELECT DAYOFYEAR('1998-02-03');
-> 34
I am jumping to conclusions, I know.

BigToque · 02-11-2005, 02:35 AM

Thanks for the help guys, I was able to figure it out.

The way my numbering system worked was like this:

year.xy

year = year
x= spring league(1), summer league(2), winter league(3)
y= preseason(1), regular season(2), playoffs(3)

basically, I just ended up making 3 integer fields as opposed to 1 float and now everything works like I want.

rayd · 02-11-2005, 02:40 AM

Glad to hear, the new schema is SO much better, having 3 fields makes a world of sense.

02-10-2005, 04:32 AM	PM User \| #1
BigToque New Coder Join Date: Jan 2005 Posts: 38 Thanks: 0 Thanked 0 Times in 0 Posts	What's wrong with this MySQL SELECT statement? I have a table called "schedule" and that table has a field called "year". "year" is a float value. All records contain one of these entries: 2004.11 2004.12 2004.13 2004.21 2004.22 2004.23 2004.31 2004.32 2004.33 This is my statement: *SELECT FROM `schedule` WHERE year = 2004.32** The query runs and is supposed to select all records from the 2004.32 season, but it doesn't select any data.

02-10-2005, 05:14 AM	PM User \| #2
hemebond Senior Coder Join Date: Jul 2004 Location: New Zealand Posts: 1,315 Thanks: 0 Thanked 2 Times in 2 Posts	Change the column from FLOAT to DECIMAL. Direct comparisons seem to work in them. Code: DECIMAL(4,2)

02-10-2005, 10:45 AM	PM User \| #3
Horus Kol Regular Coder Join Date: Nov 2004 Location: Somewhere over the rainbow Posts: 208 Thanks: 0 Thanked 0 Times in 0 Posts	just out of interest - for storing date information, why not use a timestamp? __________________ Questions are what binds the universe, Questions lead to answers, Answers lead to knowledge, Knowledge leads to wisdom, Wisdom brings more questions -- Horus Kol, 2004 :)

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

02-10-2005, 05:47 PM	PM User \| #4
rayd New to the CF scene Join Date: Feb 2005 Location: Colorado Posts: 7 Thanks: 0 Thanked 0 Times in 0 Posts	Seems like perhaps you need to store date of the year instead of month and date? If so, a timestamp might still be better, like what Horus said, then you can use : mysql> SELECT DAYOFYEAR('1998-02-03'); -> 34 I am jumping to conclusions, I know.

02-11-2005, 02:35 AM	PM User \| #5
BigToque New Coder Join Date: Jan 2005 Posts: 38 Thanks: 0 Thanked 0 Times in 0 Posts	Thanks for the help guys, I was able to figure it out. The way my numbering system worked was like this: year.xy year = year x= spring league(1), summer league(2), winter league(3) y= preseason(1), regular season(2), playoffs(3) basically, I just ended up making 3 integer fields as opposed to 1 float and now everything works like I want.

02-11-2005, 02:40 AM	PM User \| #6
rayd New to the CF scene Join Date: Feb 2005 Location: Colorado Posts: 7 Thanks: 0 Thanked 0 Times in 0 Posts	Glad to hear, the new schema is SO much better, having 3 fields makes a world of sense.