Yii2: DB DATE_FORMAT in QueryBuilder

[mariuszkrzaczkowski]

I have to create an example query using QueryBuilder, but I couldn't figure out how. Can this be done?
And if not, how can it be added to QueryBuilder?

SELECT * FROM contacts WHERE DATE_FORMAT(birthday,'%m%d') BETWEEN DATE_FORMAT('2016-11-01', '%m%d') AND DATE_FORMAT('2016-11-30', '%m%d')

I know that DATE_FORMAT supports mysql, oracle has a different function
http://www.sqlines.com/mysql-to-oracle/date_format

[dynasource]

ever tried Expression? http://www.yiiframework.com/doc-2.0/yii-db-expression.html

[yii-bot]

Thank you for your question.
In order for this issue tracker to be effective, it should only contain bug reports and feature requests.

We advise you to use our other community driven resources:

• The support forum
• The IRC chat room
• The Gitter chat room
• The Definitive Guide to Yii

If you are confident that there is a bug in the framework, feel free to
provide information on how to reproduce it. This issue will be closed for now.

_This is an automated comment, triggered by adding the label question._

[mariuszkrzaczkowski]

Yes I checked with Expression does not work.

[mariuszkrzaczkowski]

Why do you close when the problem is not solved?

[samdark]

How exactly it doesn't work? Which syntax have you tried?

[mariuszkrzaczkowski]

$row = (new \yii\db\Query())
        ->from('vtiger_contactsubdetails')
        ->where(new \yii\db\Expression("DATE_FORMAT(birthday, '%m%d') BETWEEN DATE_FORMAT('2016-11-01', '%m%d') AND DATE_FORMAT('2016-11-30', '%m%d')"))->one();

[mariuszkrzaczkowski]

Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42883]: Undefined function: 7 ERROR:  function date_format(date, unknown) does not exist
LINE 1: SELECT * FROM "vtiger_contactsubdetails" WHERE DATE_FORMAT(b...
                                                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.' in C:\www\YetiForceCRM\vendor\yii\db\Command.php:906
Stack trace:
#0 C:\www\YetiForceCRM\vendor\yii\db\Command.php(906): PDOStatement->execute()
#1 C:\www\YetiForceCRM\vendor\yii\db\Command.php(382): yii\db\Command->queryInternal('fetch', NULL)
#2 C:\www\YetiForceCRM\vendor\yii\db\Query.php(254): yii\db\Command->queryOne()
#3 C:\www\YetiForceCRM\test.php(37): yii\db\Query->one()
#4 {main}

Next exception 'yii\db\Exception' with message 'SQLSTATE[42883]: Undefined function: 7 ERROR:  function date_format(date, unknown) does not exist
LINE 1: SELECT * FROM "vtiger_contactsubdetails" WHERE DATE_FORMAT(b...
                                 in C:\www\YetiForceCRM\vendor\yii\db\Schema.php on line 636

[samdark]

Can you post the rest of the error?

[mariuszkrzaczkowski]

Updated the an earlier post

[dynasource]

https://www.drupal.org/node/991898, out of the scope of Yii?

[alex-code]

What RDBMS are you using?

[mariuszkrzaczkowski]

@dynasource
"SQLSTATE[42883]: Undefined function" This is the message from the database engine

@Alex-Code
PostgreSQL

[dynasource]

does that function exists? https://www.postgresql.org/docs/7.4/static/functions-formatting.html

[alex-code]

PostgreSQL doesn't have a DATE_FORMAT function.
You'll need to use https://www.postgresql.org/docs/8.2/static/functions-formatting.html
or create your own DATE_FORMAT function wrapper.

[yii-bot]

Thank you for your question.
In order for this issue tracker to be effective, it should only contain bug reports and feature requests.

We advise you to use our other community driven resources:

• The support forum
• The IRC chat room
• The Gitter chat room
• The Definitive Guide to Yii

If you are confident that there is a bug in the framework, feel free to
provide information on how to reproduce it. This issue will be closed for now.

_This is an automated comment, triggered by adding the label question._

[mariuszkrzaczkowski]

I know the function doesn't exist that's why I'm asking how to do it in QueryBuilder, so that it works with all available databases.
So YII doesn't support this function?

[samdark]

Nope, it does not. There's no way we can support everything.

[alexraputa]

@mariuszkrzaczkowski, try this:

$row = (new \yii\db\Query())
->from('vtiger_contactsubdetails')
->where(['between', 'birthday', '2016-11-01', '2016-11-30'])
->one();

[mariuszkrzaczkowski]

It would work, but I wanted the query to ignore the year

[alex-code]

Depending on your project you could create an install script that creates function wrappers depending on the db system.
http://okbob.blogspot.co.uk/2009/08/mysql-functions-for-postgresql.html
Part way down that page is an example.

[alexraputa]

@mariuszkrzaczkowski, if so:

$row = (new \yii\db\Query())
    ->from('vtiger_contactsubdetails')
    ->where(['between', new Expression("to_char(birthday, 'MM-DD')"), '11-01', '11-30'])
    ->one();

[mariuszkrzaczkowski]

@Alex-Code
The problem with creating functions is that not all servers allow for that, and this is an app that can be installed on any server.

@alexraputa
This is not a solution to the problem.

[samdark]

I understand what you need but that's quite specific requirement so I'd suggest implementing it in your own subclass.