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
Solved

Format safe MySql date search

Posted on 2016-08-22
4
63 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 48

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 48

Accepted Solution

by:
PortletPaul earned 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 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