Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL date string comparison

Posted on 2007-10-14
9
Medium Priority
?
3,476 Views
Last Modified: 2013-12-12
Hi X-perts,

I have a date field `Date Listed` formatted as a varchar (string). I need to select all rows from the table where the date is less than N (number of weeks) to the current date.

It would be a simple thing if the filed was formatted as a date, but I am not sure what would be easiest solution here.

Please, advise.

Thanks

A
0
Comment
Question by:andy7789
9 Comments
 
LVL 1

Expert Comment

by:sledneck
ID: 20076114
Are the strings still formatted like a date? If so you might be able to do a convert(datetime, varDateString) in your select statement...
0
 

Author Comment

by:andy7789
ID: 20076138
the string looks as 09/19/2006

I am thinking of something like

STR_TO_DATE(field,GET_FORMAT(DATE,'USA') but am not sure what the correct syntax should be
0
 

Author Comment

by:andy7789
ID: 20076154
because of those slashes I cannot use usa format
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:andy7789
ID: 20076176
Here is the correct syntax for a constant:

select * from idx_5_data, date_format(str_to_date(`11/27/2006`, "%m/%d/%Y"), "%d-%m-%Y") as new;

How can I replace the string by the field value, i.e.

select * from idx_5_data, date_format(str_to_date(`Date Listed`, "%m/%d/%Y"), "%d-%m-%Y") as new;

The above generates an error
0
 

Author Comment

by:andy7789
ID: 20076180
sorry, it was my own typo - all fixed:

select *, date_format(str_to_date(`Date Listed`, "%m/%d/%Y"), "%d-%m-%Y") as new from idx_5_data
0
 
LVL 6

Expert Comment

by:karlwilbur
ID: 20076198
try:

select *, date_format(str_to_date(`Date Listed`, "%m/%d/%Y"), "%d-%m-%Y") as new  from idx_5_data;
0
 
LVL 6

Expert Comment

by:karlwilbur
ID: 20076201
The "FROM" needs to be after the "DATE_FORMAT" because the "DATE_FORMAT" is part of the select clause.
0
 
LVL 6

Assisted Solution

by:karlwilbur
karlwilbur earned 800 total points
ID: 20076204
select *, date_format(str_to_date(my_date, "%m/%d/%Y"), "%d-%m-%Y") as new_date from data_test;
+------------+------------+
| my_date    | new_date   |
+------------+------------+
| 11/27/2006 | 27-11-2006 |
+------------+------------+
1 row in set (0.09 sec)
0
 
LVL 49

Accepted Solution

by:
Roonaan earned 1200 total points
ID: 20076249
Hello andy7789,

Try:

SELECT * FROM ?dx_5_data` WHERE str_to_date(`Date Listed`, "%m/%d/%Y")< (now() - interval 52 week )

This would however be a very slow query. You might just simply want to add a date field and then run an update query:
update `idx_5_data` set datefield = str_to_date(`Date Listed`, '%m/%d/%Y') where datefield = null
And use this new field for your selects

Regards,

Roonaan
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Suggested Courses

581 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