select date

[techker]

hey guy in my databse the date is as :04/06/2009

 

i have a querry that counts the number of installed units done

 

SELECT COUNT( `units` ) AS TotalNumber, SUM( `units` ) AS Totalunits FROM `fc`"

 

now i need to calcule the number of units done in a year and by months?

 

i tryed this

SELECT COUNT( `units` ) AS TotalNumber, SUM( `units` ) AS Totalunits
FROM `fc` 
WHERE 'date_issued' >01 /01 /2009(afther this date) gives me NULL

[Mchl]

Why do you think MySQL has a DATE, DATETIME and TIMESTAMP field types? So that people would use them and didn't run into problems like yours.

Storing a date as a string is probably the worst thing you can do to yourself. If you can switch to DATE, do it quickly. If not, you're going to do a lot of tedious string manipulation.

[techker]

well i do?i insert current date?but the format 01/01/2009

 

 

[Mchl]

This is not MySQL's DATE format.

Post your table definition using SHOW CREATE TABLE

[techker]

CREATE TABLE `fc` (

`id` int(4) NOT NULL auto_increment,

`company` varchar(200) NOT NULL,

`contact` varchar(20) NOT NULL,

`number` varchar(20) NOT NULL,

`units` varchar(20) NOT NULL,

`PTO` varchar(20) NOT NULL,

`store` varchar(20) NOT NULL,

`date_issued` varchar(20) NOT NULL,

`date_done` varchar(20) NOT NULL,

`times` varchar(20) NOT NULL,

`custom_rate` varchar(20) NOT NULL,

`notes` varchar(200) NOT NULL,

`travel_times` varchar(20) NOT NULL,

`travel_charge` varchar(20) NOT NULL,

`paid` varchar(30) NOT NULL,

`month` varchar(25) NOT NULL,

`year` varchar(20) NOT NULL,

`client_id` varchar(20) NOT NULL,

`service_call` varchar(100) NOT NULL,

`done` varchar(50) NOT NULL,

PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=164 DEFAULT CHARSET=latin1

[Mchl]

`date_issued` varchar(20) NOT NULL,

 

As you can see the column type for date_issued is VARCHAR. This means MySQL does not know it's a date. It thinks it's just a string. Same for date_done.

Also VARCHAR is not some kind of magic datatype that's applicable for each and every kind of data... yet almost all column in this table use it... Maybe you should rethink it?

[techker]

ok so its to late to think it over.already lots of data in there.

 

now i have notice that when i did this i added a line month.

 

so is there a way to select  all from fc were month =january and date is befor 01/01/2009

 

 

[Mchl]

164 rows is not much, and you can do the change in four queries.

 

1. Create a column date_issued_temp (DATE)

2. Copy the content of date_issued to date_issued_temp using STR_TO_DATE() function to convert your strings to date.

3. Drop the date_issued column

4. Rename date_issued_temp to date_issued

[xtopolis]

Be sure to backup the database before doing this!

[Mchl]

Um.. yes... That goes without saying...