You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Suresh Krishnan <sk...@csod.com.INVALID> on 2020/02/20 05:54:51 UTC
Calcite equivalent for Select CONVERT date formatter
Hi Team,
We are having trouble finding the right approach to convert the date fields using calcite.
We are looking for something that will give us the below SQL equivalent
SELECT CONVERT(varchar(12), GETDATE(), 101) - 06/29/2009
We have tried creating a rexNode using the below method. But it didn't yield the expected result.
RelDataType type = builder.getTypeFactory().createSqlType(SqlTypeName.DATE);
RexNode rexNode2 = builder.getRexBuilder().makeCall(type, SqlStdOperatorTable.CONVERT, rexNodes);
Could you please help with this request?
Thanks,
Suresh Krishnan
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
RE: RE: Re: Calcite equivalent for Select CONVERT date formatter
Posted by Hrudaya Reddy <hr...@csod.com.INVALID>.
I think this should help us Danny. Thank you.
Regards,
Hrudaya
-----Original Message-----
From: Danny Chan <yu...@gmail.com>
Sent: Thursday, February 20, 2020 8:13 PM
To: dev@calcite.apache.org
Subject: [EXTERNAL] RE: Re: Calcite equivalent for Select CONVERT date formatter
Is this [1] what you need ?
[1] https://urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache.org_jira_browse_CALCITE-2D2980&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=RLrkWGum355sIOTdB1j9pu0dFKIKXRTXyclBvURvTkA&s=tSLoGDqVjvLco51HdaP86J2ZT7ijZjLkj6AhkrTEpHI&e=
Best,
Danny Chan
在 2020年2月21日 +0800 AM8:23，Hrudaya Reddy <hr...@csod.com.invalid>，写道：
> Hi Julian and Danny,
>
> We are basically looking for the equivalent SQL Server CONVERT() function which converts a value (of any type) into a specified datatype.
>
> Example: Convert from datetime to varchar
>
> If 'SELECT [date] FROM users' returns 2013-05-14 23:05:29.813, we want to convert it into different formats.
> 'SELECT CONVERT(varchar, [date]) FROM users' returns May 14 2013
> 11:05PM or 'SELECT CONVERT(date, [date]) FROM users' returns
> 2013-05-14 or 'SELECT CONVERT(varchar, [date], 112) FROM users'
> returns 20130514 (112 for ISO format)
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.w3schools.com
> _sql_func-5Fsqlserver-5Fconvert.asp&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&
> r=MjX6IuQXq8W7yQ_nB4S_UQ&m=RLrkWGum355sIOTdB1j9pu0dFKIKXRTXyclBvURvTkA
> &s=3qDjyJtEvqn10ap6LtxW0954HOgk-HihopBQSfeZJf0&e=
>
> Regards,
> Hrudaya
>
> -----Original Message-----
> From: Julian Hyde <jh...@apache.org>
> Sent: Thursday, February 20, 2020 10:21 AM
> To: dev <de...@calcite.apache.org>
> Subject: [EXTERNAL] Re: Calcite equivalent for Select CONVERT date
> formatter
>
> Which DBMS is that SQL for?
>
> In standard SQL and Calcite (and Oracle [1]), the CONVERT function changes the encoding of a character string. But I don’t think you mean that.
>
> Julian
>
> [1]
> https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_c
> d_B28359-5F01_server.111_b28286_functions027.htm-23SQLRF00620&d=DwIFaQ
> &c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=bFCa9GfhhEdjVMa4f
> E9ZiYcA0gn4rY_f7WQ8ojbJPs8&s=BZzhOGfcWoEr7-2PzlY9EaImQR5IyxzH5V_NQs-SU
> OM&e=
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_
> cd_B28359-5F01_server.111_b28286_functions027.htm-23SQLRF00620&d=DwIFa
> Q&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=bFCa9GfhhEdjVMa4
> fE9ZiYcA0gn4rY_f7WQ8ojbJPs8&s=BZzhOGfcWoEr7-2PzlY9EaImQR5IyxzH5V_NQs-S
> UOM&e= >
>
> > On Feb 20, 2020, at 6:25 AM, Danny Chan <yu...@gmail.com> wrote:
> >
> > From varchar to date?
> >
> > Suresh Krishnan <sk...@csod.com.invalid>于2020年2月20日 周四下午1:55写道：
> >
> > > Hi Team,
> > >
> > > We are having trouble finding the right approach to convert the
> > > date fields using calcite.
> > >
> > > We are looking for something that will give us the below SQL
> > > equivalent SELECT CONVERT(varchar(12), GETDATE(), 101) -
> > > 06/29/2009
> > >
> > > We have tried creating a rexNode using the below method. But it
> > > didn't yield the expected result.
> > > RelDataType type =
> > > builder.getTypeFactory().createSqlType(SqlTypeName.DATE);
> > > RexNode rexNode2 = builder.getRexBuilder().makeCall(type,
> > > SqlStdOperatorTable.CONVERT, rexNodes);
> > >
> > >
> > > Could you please help with this request?
> > >
> > > Thanks,
> > > Suresh Krishnan
> > > This message, together with any attachments, is intended only for
> > > the use of the individual or entity to which it is addressed and
> > > may contain confidential and/or privileged information. If you are
> > > not the intended recipient(s), or the employee or agent
> > > responsible for delivery of this message to the intended
> > > recipient(s), you are hereby notified that any dissemination,
> > > distribution or copying of this message, or any attachment, is
> > > strictly prohibited. If you have received this message in error,
> > > please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
> > >
>
> ----------------------------------------------------------------------
> *EXTERNAL MESSAGE WARNING: This email originated from outside of
> Cornerstone. Do not click links or open attachments unless you
> recognize the sender and know the content is safe. Please see this
> wiki for more information on email safety:
> https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
> This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
----------------------------------------------------------------------
*EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
RE: Re: Calcite equivalent for Select CONVERT date formatter
Posted by Danny Chan <yu...@gmail.com>.
Is this [1] what you need ?
[1] https://issues.apache.org/jira/browse/CALCITE-2980
Best,
Danny Chan
在 2020年2月21日 +0800 AM8:23，Hrudaya Reddy <hr...@csod.com.invalid>，写道：
> Hi Julian and Danny,
>
> We are basically looking for the equivalent SQL Server CONVERT() function which converts a value (of any type) into a specified datatype.
>
> Example: Convert from datetime to varchar
>
> If 'SELECT [date] FROM users' returns 2013-05-14 23:05:29.813, we want to convert it into different formats.
> 'SELECT CONVERT(varchar, [date]) FROM users' returns May 14 2013 11:05PM
> or 'SELECT CONVERT(date, [date]) FROM users' returns 2013-05-14
> or 'SELECT CONVERT(varchar, [date], 112) FROM users' returns 20130514 (112 for ISO format)
>
> https://www.w3schools.com/sql/func_sqlserver_convert.asp
>
> Regards,
> Hrudaya
>
> -----Original Message-----
> From: Julian Hyde <jh...@apache.org>
> Sent: Thursday, February 20, 2020 10:21 AM
> To: dev <de...@calcite.apache.org>
> Subject: [EXTERNAL] Re: Calcite equivalent for Select CONVERT date formatter
>
> Which DBMS is that SQL for?
>
> In standard SQL and Calcite (and Oracle [1]), the CONVERT function changes the encoding of a character string. But I don’t think you mean that.
>
> Julian
>
> [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28286_functions027.htm-23SQLRF00620&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=bFCa9GfhhEdjVMa4fE9ZiYcA0gn4rY_f7WQ8ojbJPs8&s=BZzhOGfcWoEr7-2PzlY9EaImQR5IyxzH5V_NQs-SUOM&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28286_functions027.htm-23SQLRF00620&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=bFCa9GfhhEdjVMa4fE9ZiYcA0gn4rY_f7WQ8ojbJPs8&s=BZzhOGfcWoEr7-2PzlY9EaImQR5IyxzH5V_NQs-SUOM&e= >
>
> > On Feb 20, 2020, at 6:25 AM, Danny Chan <yu...@gmail.com> wrote:
> >
> > From varchar to date?
> >
> > Suresh Krishnan <sk...@csod.com.invalid>于2020年2月20日 周四下午1:55写道：
> >
> > > Hi Team,
> > >
> > > We are having trouble finding the right approach to convert the date
> > > fields using calcite.
> > >
> > > We are looking for something that will give us the below SQL equivalent
> > > SELECT CONVERT(varchar(12), GETDATE(), 101) -
> > > 06/29/2009
> > >
> > > We have tried creating a rexNode using the below method. But it
> > > didn't yield the expected result.
> > > RelDataType type =
> > > builder.getTypeFactory().createSqlType(SqlTypeName.DATE);
> > > RexNode rexNode2 = builder.getRexBuilder().makeCall(type,
> > > SqlStdOperatorTable.CONVERT, rexNodes);
> > >
> > >
> > > Could you please help with this request?
> > >
> > > Thanks,
> > > Suresh Krishnan
> > > This message, together with any attachments, is intended only for the
> > > use of the individual or entity to which it is addressed and may
> > > contain confidential and/or privileged information. If you are not
> > > the intended recipient(s), or the employee or agent responsible for
> > > delivery of this message to the intended recipient(s), you are hereby
> > > notified that any dissemination, distribution or copying of this
> > > message, or any attachment, is strictly prohibited. If you have
> > > received this message in error, please immediately notify the sender
> > > and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
> > >
>
> ----------------------------------------------------------------------
> *EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
> This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
RE: Re: Calcite equivalent for Select CONVERT date formatter
Posted by Hrudaya Reddy <hr...@csod.com.INVALID>.
Hi Julian and Danny,
We are basically looking for the equivalent SQL Server CONVERT() function which converts a value (of any type) into a specified datatype.
Example: Convert from datetime to varchar
If 'SELECT [date] FROM users' returns 2013-05-14 23:05:29.813, we want to convert it into different formats.
'SELECT CONVERT(varchar, [date]) FROM users' returns May 14 2013 11:05PM
or 'SELECT CONVERT(date, [date]) FROM users' returns 2013-05-14
or 'SELECT CONVERT(varchar, [date], 112) FROM users' returns 20130514 (112 for ISO format)
https://www.w3schools.com/sql/func_sqlserver_convert.asp
Regards,
Hrudaya
-----Original Message-----
From: Julian Hyde <jh...@apache.org>
Sent: Thursday, February 20, 2020 10:21 AM
To: dev <de...@calcite.apache.org>
Subject: [EXTERNAL] Re: Calcite equivalent for Select CONVERT date formatter
Which DBMS is that SQL for?
In standard SQL and Calcite (and Oracle [1]), the CONVERT function changes the encoding of a character string. But I don’t think you mean that.
Julian
[1] https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28286_functions027.htm-23SQLRF00620&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=bFCa9GfhhEdjVMa4fE9ZiYcA0gn4rY_f7WQ8ojbJPs8&s=BZzhOGfcWoEr7-2PzlY9EaImQR5IyxzH5V_NQs-SUOM&e= <https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.oracle.com_cd_B28359-5F01_server.111_b28286_functions027.htm-23SQLRF00620&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=bFCa9GfhhEdjVMa4fE9ZiYcA0gn4rY_f7WQ8ojbJPs8&s=BZzhOGfcWoEr7-2PzlY9EaImQR5IyxzH5V_NQs-SUOM&e= >
> On Feb 20, 2020, at 6:25 AM, Danny Chan <yu...@gmail.com> wrote:
>
> From varchar to date?
>
> Suresh Krishnan <sk...@csod.com.invalid>于2020年2月20日 周四下午1:55写道：
>
>> Hi Team,
>>
>> We are having trouble finding the right approach to convert the date
>> fields using calcite.
>>
>> We are looking for something that will give us the below SQL equivalent
>> SELECT CONVERT(varchar(12), GETDATE(), 101) -
>> 06/29/2009
>>
>> We have tried creating a rexNode using the below method. But it
>> didn't yield the expected result.
>> RelDataType type =
>> builder.getTypeFactory().createSqlType(SqlTypeName.DATE);
>> RexNode rexNode2 = builder.getRexBuilder().makeCall(type,
>> SqlStdOperatorTable.CONVERT, rexNodes);
>>
>>
>> Could you please help with this request?
>>
>> Thanks,
>> Suresh Krishnan
>> This message, together with any attachments, is intended only for the
>> use of the individual or entity to which it is addressed and may
>> contain confidential and/or privileged information. If you are not
>> the intended recipient(s), or the employee or agent responsible for
>> delivery of this message to the intended recipient(s), you are hereby
>> notified that any dissemination, distribution or copying of this
>> message, or any attachment, is strictly prohibited. If you have
>> received this message in error, please immediately notify the sender
>> and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
>>
----------------------------------------------------------------------
*EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
Re: Calcite equivalent for Select CONVERT date formatter
Posted by Julian Hyde <jh...@apache.org>.
Which DBMS is that SQL for?
In standard SQL and Calcite (and Oracle [1]), the CONVERT function changes the encoding of a character string. But I don’t think you mean that.
Julian
[1] https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm#SQLRF00620 <https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm#SQLRF00620>
> On Feb 20, 2020, at 6:25 AM, Danny Chan <yu...@gmail.com> wrote:
>
> From varchar to date?
>
> Suresh Krishnan <sk...@csod.com.invalid>于2020年2月20日 周四下午1:55写道：
>
>> Hi Team,
>>
>> We are having trouble finding the right approach to convert the date
>> fields using calcite.
>>
>> We are looking for something that will give us the below SQL equivalent
>> SELECT CONVERT(varchar(12), GETDATE(), 101) -
>> 06/29/2009
>>
>> We have tried creating a rexNode using the below method. But it didn't
>> yield the expected result.
>> RelDataType type =
>> builder.getTypeFactory().createSqlType(SqlTypeName.DATE);
>> RexNode rexNode2 = builder.getRexBuilder().makeCall(type,
>> SqlStdOperatorTable.CONVERT, rexNodes);
>>
>>
>> Could you please help with this request?
>>
>> Thanks,
>> Suresh Krishnan
>> This message, together with any attachments, is intended only for the use
>> of the individual or entity to which it is addressed and may contain
>> confidential and/or privileged information. If you are not the intended
>> recipient(s), or the employee or agent responsible for delivery of this
>> message to the intended recipient(s), you are hereby notified that any
>> dissemination, distribution or copying of this message, or any attachment,
>> is strictly prohibited. If you have received this message in error, please
>> immediately notify the sender and delete the message, together with any
>> attachments, from your computer. Thank you for your cooperation.
>>
Re: Calcite equivalent for Select CONVERT date formatter
Posted by Danny Chan <yu...@gmail.com>.
From varchar to date?
Suresh Krishnan <sk...@csod.com.invalid>于2020年2月20日 周四下午1:55写道：
> Hi Team,
>
> We are having trouble finding the right approach to convert the date
> fields using calcite.
>
> We are looking for something that will give us the below SQL equivalent
> SELECT CONVERT(varchar(12), GETDATE(), 101) -
> 06/29/2009
>
> We have tried creating a rexNode using the below method. But it didn't
> yield the expected result.
> RelDataType type =
> builder.getTypeFactory().createSqlType(SqlTypeName.DATE);
> RexNode rexNode2 = builder.getRexBuilder().makeCall(type,
> SqlStdOperatorTable.CONVERT, rexNodes);
>
>
> Could you please help with this request?
>
> Thanks,
> Suresh Krishnan
> This message, together with any attachments, is intended only for the use
> of the individual or entity to which it is addressed and may contain
> confidential and/or privileged information. If you are not the intended
> recipient(s), or the employee or agent responsible for delivery of this
> message to the intended recipient(s), you are hereby notified that any
> dissemination, distribution or copying of this message, or any attachment,
> is strictly prohibited. If you have received this message in error, please
> immediately notify the sender and delete the message, together with any
> attachments, from your computer. Thank you for your cooperation.
>