Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format safe MySql date search

Posted on 2016-08-22
4
Medium Priority
?
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

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

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

705 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