ASP, MySQL and Dates!

Ok I am using ASP to pull from a MySQL database.  My problem is that MySQL doens't hold Date values as "real dates" like Access does.  I am trying to pull rows between certain dates and it is not working.

Let's say i'm trying to pull data only between 3/1/2006 and 3/31/2006.  and I'm using this code:
<--- CODE --->
Dim sConnection, objConn , RS
sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=ASP_test; UID=root;PASSWORD=; OPTION=3"
Set objConn = Server.CreateObject("ADODB.Connection")

Set RS = objConn.Execute("SELECT * FROM fastpay WHERE Date BETWEEN '" & begin_date & "' AND '" & end_date & "' ORDER BY DATE ASC, ID DESC")
<--- /CODE --->

When I execute the code I get all the correct dates BUT it includes all years!  Like it's not reading it as a date but just as text.  I would get:

I've tried DATE_FORMAT for MySQL but it doens't seem to work either.  Any help with this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You don't mention the formatting of the date you use within the SQL string. Maybe that's the problem.

For date fields you should use yyyy-mm-dd and for datetime use yyyy-mm-dd hh:mm:ss.

So your SQL statement should look like:

SELECT * FROM fastpay WHERE Date BETWEEN '2006-01-03' AND '2006-01-31' ORDER BY DATE ASC, ID DESC
gillwebAuthor Commented:
My problem here is that the Date column in my database is setup as TEXT.datatype and not DATE.datatype. It's for an Intranet and When I originally built the database years ago (before I knew too much MySQL) I just set it up as TEXT but now if I try and change it over to DATE it over writes all the dates and enters 0000-00-00 in thier place. And the database has over 10,000 rows in it so manually changing them is just impossible. Is there a way to change that column's datatype to DATE without losiing the current dates which are formatted like 00-00-0000? Or is there a way for me to pull from that column and have it converted to DATE to use in the search?
It makes the most sense to convert your current data to proper date types. In the future you may want to do additional querying of the data using date functionality, so your current string "date" values are going to cause problems.

> Is there a way to change that column's datatype to DATE without losiing the
> current dates which are formatted like 00-00-0000?

I don't think you'll be able to simply change the column type. If the dates had the current format then this might work but not in your case.

I suggest you create a new column of the type date. You could then run a query to select from your current column into your new column and in the process do some string manipulation to format the date string correctly. Or perhaps it would be easier to do this with a little script.

Once you have the new column with the correct date-type data you could delete or rename the original column then rename your new column with the original column name.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

gillwebAuthor Commented:
That sounds like a good idea so I have made new DATE.datatype column named "new_date".  The old data is formattted like this 4/5/2006 in the old column named "date".  I'm not 100% sure how to write a script in ASP to take the old date, convert it to the new date and add to newcolumn.  Can you give me some code that would do this for me?

Can you send me your database format sql from phpmyadmin. I would need just a few enteries, not all the 10,000 recoreds that you have, just the database tables structure and a few enteries. I would write a PHP or ASP code for you to copy the old date to the new column you have done. Lets try to do this.

After we suceed, you can remove the old text date column.

Fahd Murtaza
gillwebAuthor Commented:
Here's my SQL dump and I need ASP code for the conversion not PHP.

-- phpMyAdmin SQL Dump
-- version
-- Host: localhost
-- Server version: 4.1.7
-- PHP Version: 4.3.9
-- Database: `asp_test`

-- --------------------------------------------------------

-- Table structure for table `fastpay`

CREATE TABLE `fastpay` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` text NOT NULL,
  `Date` text NOT NULL,
  `Acct_Num` text NOT NULL,
  `Rout_Num` text NOT NULL,
  `Save_Account` text NOT NULL,
  `Amount` text NOT NULL,
  `Saved` tinyint(4) NOT NULL default '0',
  `Done` text NOT NULL,
  `empl_fname` text NOT NULL,
  `new_date` date NOT NULL default '0000-00-00',

-- Dumping data for table `fastpay`

INSERT INTO `fastpay` (`ID`, `Name`, `Date`, `Acct_Num`, `Rout_Num`, `Save_Account`, `Amount`, `Saved`, `Done`, `empl_fname`, `new_date`) VALUES (11126, 'JEFF THOMAS', '4/4/2006', '', '', '', '$57.00', 1, 'Y', 'Dave', '0000-00-00'),
(11127, 'PATRICK STUKES', '4/4/2006', '', '', '', '$140.00', 1, 'Y', 'Dave', '0000-00-00'),
(11128, 'STEPHEN SKINNER', '4/4/2006', '', '', '', '$200.00', 1, 'Y', 'Jenn', '0000-00-00'),
(11129, 'DEE CLARK', '4/4/2006', '', '', '', '$277.75', 1, 'Y', 'Jenn', '0000-00-00'),
(11130, 'MICHELLE TURNER', '4/4/2006', '', '', '', '$106.00', 1, 'Y', 'Jenn', '0000-00-00'),
(11131, 'TRICIA OLIVER', '4/4/2006', '003814682475', '021200339', 'on', '$145.78', 2, 'Y', 'Dave', '0000-00-00'),
Check this

You can use this to format the string for the old date in text format and make a batch update for the myql table's new column that you have as new_date.

Hope it helps.

Fahd Murtaza

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gillwebAuthor Commented:
I got it guys!  Thanks!  I didn't realize at first but I just used an SQL query to take the date info in my old table and parse it into the new table like this...

update fastpay set new_date = str_to_date(date, '%c/%e/%Y')

wow I tried the same thing and I just searched through my knowlegdge base and I didn't find this functions. Great that you solved it.

Fahd Murtaza
gillwebAuthor Commented:
yeah I felt the same easy it was actually hard! :-)  I split the points accordingly, thanks for the ideas and help guys.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.