Date function for datetime field does not work

English
, ,
#1

I’m using an .odb file for the frontend of a MySQL database. I have a datetime field that, when shown in a certain query, I want to only see the date. Typically in MySQL, this is done using DATE(DATETIME_FIELD).

However, whenever I use that in a query within the .odb file, I get an error saying my SQL syntax is incorrect. Funny thing is, when I execute it under the Tools>SQL dialog, it works perfectly. Has anyone seen anything like this?

Version: 7.0.6.2 (x64)
Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b
CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

#2

Base splits any DATETIME field into the date and the time porition when used in the user interface (that is in a form or report).
On table level, something like CAST(“datetime_field” AS DATE) AS “Date” should do the trick.

#3

I tried to use this on the query level, and instead of a date, I got numbers like “43517”.

#4

Format this as Date and it will do what you want.

#5

I don’t know exactly how to do this. I tried

FORMAT(RECEIVING.DATERECEIVED, '%Y-%m-%d')

And I still get a SQL syntax error.

#6

It is only temporary: Right mouse click on the header of the column of the query. There you could format the column.

If you open the query in a form you will get this formatting as permanent formatting.

In MySQL should work DATE_FORMAT(Date,’%Y-%m-%d’). If this doesn’t work I would try it with CONCAT and getting a string of the date.

You could also execute the query in direct SQL with DATE() as you have written in you first post.

#7

Yes, I mentioned that in my question. If that approach had worked for my needs, I wouldn’t have bothered everyone by asking the question. I need a query I can use in a report.

#8

A report will format this field the right way. Set the format in the texfield of the report.

#9

Okay, thanks! That takes care of my problem.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.