?
Solved

SQL Syntax for MySQL

Posted on 2008-11-15
8
Medium Priority
?
332 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:pda4me
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22966923
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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22966933
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
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22967056
Try this..


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

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 22967153
If you do not want to change the date format you are using, you can use this query to get the current date dynamically:

SELECT sStreetFullName
FROM `Residential_RESI`
WHERE `sListingDate` = date_format(curdate(),'%m/%d/%y')

To change the date format, so that it easier to use the date, you could do this:

ALTER TABLE sStreetFullName ADD dListingDate DATE;

The above statement adds a column to the table. To populate it from your existing date column, use this:

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

Check the results, and then you can drop the string column:

ALTER TABLE sStreetFullName DROP sListingDate;
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22967175
Sorry, the update statement should be:

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

Expert Comment

by:Roger Baklund
ID: 22967319
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;
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22967323
...and the update was wrong again... (argh!)

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

Author Closing Comment

by:pda4me
ID: 31517075
Thanks!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question