[Webinar] Streamline your web hosting managementRegister Today

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

Format safe MySql date search

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
Paer Toernell
Asked:
Paer Toernell
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
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
 
Paer ToernellAuthor Commented:
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
 
PortletPaulCommented:
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
 
Paer ToernellAuthor Commented:
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

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

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