[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

varchar date sort by date

I have a db that has a date stored as a varchar.
I know this is bad, but it is not going to change.

How do I sort the results by date?

select * from table1 order by date?

Thanks
0
jackjohnson44
Asked:
jackjohnson44
  • 5
  • 4
1 Solution
 
todd_farmerCommented:
SELECT * FROM table1 ORDER BY STR_TO_DATE(date, '%m/%d/%Y')
0
 
jackjohnson44Author Commented:
that didn't work

my date format is 3/29/2006
0
 
todd_farmerCommented:
The date conversion will work for that format:


mysql> select STR_TO_DATE('3/29/2006', '%m/%d/%Y');
+--------------------------------------+
| STR_TO_DATE('3/29/2006', '%m/%d/%Y') |
+--------------------------------------+
| 2006-03-29                           |
+--------------------------------------+
1 row in set (0.01 sec)

Can you give me a better understanding of what you mean when you say it doesn't work?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
jackjohnson44Author Commented:
I am getting this error

select * from subscriptions where type='Paid' order by STR_TO_DATE(`date`, '%m/%d/%Y')

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(`date`, '%m/%d/%Y')' at line 1
0
 
todd_farmerCommented:
What version of MySQL are you running?  It seems like it may be an older version.
0
 
jackjohnson44Author Commented:
Thanks for you help.

I am not sure, how do I tell?

If I am running an older version, is there any way around this?
0
 
todd_farmerCommented:
SELECT VERSION();

should give you the version.

Let's see what you're working with, then perhaps we can look for workarounds.
0
 
jackjohnson44Author Commented:
I just conveted the type to date.

How do I insert the current date into an insert query?
0
 
todd_farmerCommented:
INSERT INTO table1 (date) VALUES (NOW());
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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