asked on

SQL Syntax for MySQL

I am using the current query with a hardcoded date:

SELECT sStreetFullName
FROM `Residential_RESI`
WHERE `sListingDate` = '11/01/08'

How do I substitute the manually entered date with the current date dynamically in my sql query?

Guy Hengel [angelIII / a3]

assuming that your field sListingDate is date as data type, you HAVE to use YYYY-MM-DD as format!
if it is varchar, you made a data type choice error.

now, what date value do you want to get, actually, dynamically?

Guy Hengel [angelIII / a3]

you might find your luck in the list of the functions for date (and time):
dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Umesh

Try this..

SELECT sStreetFullName
FROM `Residential_RESI`
WHERE `sListingDate` = STR_TO_DATE(REPLACE('11/01/08','/','-'),'%m-%d-%y');

Open in new window

ASKER CERTIFIED SOLUTION

Roger Baklund

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger Baklund

Sorry, the update statement should be:

UPDATE sStreetFullName SET dListingDate = STR_TO_DATE(sListingDate,'%m/%d/%y');

Roger Baklund

Sorry again, your table is named Residential_RESI...!

ALTER TABLE Residential_RESI ADD dListingDate DATE;
UPDATE Residential_RESI SET dListingDate = STR_TO_DATE(sListingDate,'%m-%d-%y');
ALTER TABLE Residential_RESI DROP sListingDate;

Roger Baklund

...and the update was wrong again... (argh!)

UPDATE Residential_RESI SET dListingDate = STR_TO_DATE(sListingDate,'%m/%d/%y');

pda4me

ASKER

Thanks!