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 <dev@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 <yuzhao....@gmail.com> wrote: > > From varchar to date? > > Suresh Krishnan <skrish...@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.
