Date_Format Not Working Properly

[rasinc]

I've created a query with the query builder (great tool BTW), and am now manually editing it. It will eventually go into a program I am working on. It keeps giving me an error when I add date formatting. The date field is datetime with time being all zeros.

Code:

SELECT
DATE_FORMAT (`tablename`.`dtDate`, '%Y%m%d') AS `CheckDate`
FROM
`database`.`tablename`
ORDER BY `CheckDate` ASC;

Because of the error, I stripped everything out but the above. The statement above still gets an error

Quote:

Error Code: 1305

FUNCTION database.Date_Format does not exist

Execution Time : 0 sec

Transfer Time : 0 sec

Total Time : 0 sec

It works fine without the DATE_FORMAT function if I just let it pull the date field as is. However, I want it in the final format of YYYYMMDD without having to manipulate it later. eg. 20120314 for today.

Anyone have any ideas why it is not working? I pulled the syntax from the MySQL manaul online. I am using SQLyog 9.62 Ultimate.

TIA, rasinc

[rasinc]

'rasinc' wrote:

Code:

SELECT
DATE_FORMAT (`tablename`.`dtDate`, '%Y%m%d') AS `CheckDate`
FROM
`database`.`tablename`
ORDER BY `CheckDate` ASC;

Proper code

Code:

SELECT
DATE_FORMAT(`tablename`.`dtDate`, '%Y%m%d') AS `CheckDate`
FROM
`database`.`tablename`
ORDER BY `CheckDate` ASC;

Sorry, never mind. I've got it now. Had no idea the space after DATE_FORMAT was critically bad! It's not an issue for LEFT and RIGHT functions.

[peterlaursen]

I don't believe this is the problem! This works fine for me:

Code:

SELECT
DATE_FORMAT (`dttest`.`dt1`, '%Y%m%d') AS `CheckDate` — <– note the SPACE
FROM
`test`.`dttest`
ORDER BY `CheckDate` ASC;

(`test` is a database, `dttest` is a table and `dt1` is a DATETIME column)

I simply think you mismatched names of database, table and column! Actually it looks like you typed 'database' literally instead of the actual database name.

[rasinc]

Hi Peter,

Thanks for responding. I did just type in the syntax and used generic names. However, I have done several tests and the Right, Left and Replace functions allow a space before the parenthesis. Concat, Date_Format and Format do not allow a space for some reason. Now that I know, it works fine.

I should mention, I created the basic query using the Query Builder, so I was not typing the database, table or field names myself.

I'm just used to using spaces when I do Crystal Reports programming and need to remember when in SQLyog not to use them. I am getting the correct result set now.

[peterlaursen]

I have to insist that it does not matter what client you use. It is the server that returns this error. All 4-digit error numbers starting with “1” are.

[Author]

Posts