Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Format safe MySql date search

Posted on 2016-08-22
4
Medium Priority
?
78 Views
Last Modified: 2016-08-23
Im searching for rows in a database where the date format can variate, depending on the date format in the end-users computer. How do i write a Mysql query from date to date that regardless of the computers time format always will work?
0
Comment
Question by:Sunsales
  • 2
  • 2
4 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41766027
A column in your table that has a data type of DATE, DATETIME or TIMESTAMP is NOT stored in a format

So what data type is that column? e.g. VARCHAR or CHAR

Can you provide some samples from that column?
0
 

Author Comment

by:Sunsales
ID: 41766391
Datatype is "Date". I need to serach fron date a to date b regarless of what date-format the end users computer have.

Example:

SELECT * FROM tablename 
WHERE orderDate BETWEEN '2012-12-25' AND '2012-12-25 23'

Open in new window


wont work if the computer have english date format ( '18/10/2013' ). Im looking for a way to specify the date regarless of the date-setting on that particular computer.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 41766726
Please refer to to topic of "date literals" in the MySQL documentation (here)

For your sql queries always use yyyy-mm-dd or yyyymmdd and don't worry about the local workstation date settings

SELECT * FROM tablename
WHERE orderDate = '2012-12-25'

or

SELECT * FROM tablename
WHERE orderDate = '20121225'

or

SELECT * FROM tablename
WHERE orderDate = 20121225

nb: With a data type is "Date" you don't need to worry about ant time unit below a day
(hours, minutes, seconds or sub-seconds are all irrelevant to a "date" data type)

also note: Between is NOT a good thing to use with date/time information, for more on this topic please see: "Beware of Between"
1
 

Author Comment

by:Sunsales
ID: 41766760
Yes you are correct, im barking under the wrong tree. Its Delphi who change the date format depending on date format in computer, not MySql.

This works:

MyFrom:= formatdatetime('yyymmdd', cxDateEdit1.Date);
      MyTo:=   formatdatetime('yyymmdd', cxDateEdit2.Date);

Open in new window

0

Featured Post

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.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

927 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