I think this should help us Danny. Thank you. Regards, Hrudaya
-----Original Message----- From: Danny Chan <[email protected]> Sent: Thursday, February 20, 2020 8:13 PM To: [email protected] 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 <[email protected]>，写道： > 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 <[email protected]> > Sent: Thursday, February 20, 2020 10:21 AM > To: dev <[email protected]> > 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 <[email protected]> wrote: > > > > From varchar to date? > > > > Suresh Krishnan <[email protected]>于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
