Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1523
  • Last Modified:

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")
objConn.Open(sConnection)

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:
3/1/2004
3/1/2005
3/2/2005
3/3/2006
3/5/2001
etc.

I've tried DATE_FORMAT for MySQL but it doens't seem to work either.  Any help with this?
0
gillweb
Asked:
gillweb
  • 5
  • 3
  • 2
2 Solutions
 
deighcCommented:
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
0
 
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?
0
 
deighcCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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?
0
 
FahdmurtazaCommented:
Hi

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.

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

-- phpMyAdmin SQL Dump
-- version 2.8.0.2
-- http://www.phpmyadmin.net
--
-- 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',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11138 ;

--
-- 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'),
0
 
FahdmurtazaCommented:
Check this

http://www.aspfree.com/c/a/ASP-Code/Format-DateTime-Function--fmtDateTime-by-Kevin-Turner/

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.

Regards,
Fahd Murtaza
0
 
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')

0
 
FahdmurtazaCommented:
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.

Regards,
Fahd Murtaza
0
 
gillwebAuthor Commented:
yeah I felt the same way...so easy it was actually hard! :-)  I split the points accordingly, thanks for the ideas and help guys.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now