hi,
i need to build a query with the convert function in the select and in the group by statement, for example:
SELECT
SUM(DEPOSITS.DEPOSITAMOUNT)
, CONVERT(Datetime,DEPOSITS.CREATEDDATE,101) AS date_conv
FROM
master_gp.dbo.DEPOSITS DEPOSITS
GROUP BY ** CONVERT**(Datetime,DEPOSITS.CREATEDDATE,101)
ORDER BY
DEPOSITS.CREATEDDATE ASC
now, i dont see an option for that in the query builder online (i mean the visual tool).
how can i let my data guy (who does not program sql) to do that manipulation.
Thanks
Use the calculated fields editor to add the convert function. See attached.
Open up the calculated fields editor from the tool bar.
Add a name for the field and click the plus sign.
Open up the editor and build the funtion
Select the table to associate it to.
This will add a calculated fields column.
You can then add the group by.
I have also attached a saved Query Builder file useing Northwind db as example.
Use the calculated fields editor to add the convert function. See attached.
Open up the calculated fields editor from the tool bar.
Add a name for the field and click the plus sign.
Open up the editor and build the funtion
Select the table to associate it to.
This will add a calculated fields column.
You can then add the group by.
I have also attached a saved Query Builder file useing Northwind db as example.
Thank you for the detailed response! After searching extensively for the appropriate syntax to use with the CONVERT function, I came to the conclusion that it should be written as you described above. This does not, however, work in my query! I am not sure if it is related to my version of Toad (2.5.0.913) or the database itself (I think this may be possible after trying to recreate your example above in the sample database included with my organization’s TDA - the CONVERT function was not even recognized and was not in the list of available functions), but the description in the calculated fields editor is even different than yours…
Any suggestions?
I should note that when I have the calculation set as intended, CONVERT (datetime, TBRACCD.TBRACCD_FEED_DATE, 101), I get the error message: Toad for Data Analysts: ORA-00904: “DATETIME”: invalid identifier
If I change DATETIME to DATE, I get: Toad for Data Analysts: ORA-00936: missing expression
Message was edited by: -Wes-
I think I caused you some confusion. I sent you a file that was for SQL Server 2005 and uses different arguements.
You now know where to enter a convert statement in the Query Builder.
Here is my suggestion. Go to the View menu and open up the Code Snippets. These are common functions for all the DB types with links to the help topics. Find convert for the DB type you are using. Select it and at the bottom of the docked window is a hyperlink to the help file. This way you can have the reference for the convert function as well as others.
I think I caused you some confusion. I sent you a file that was for SQL Server 2005 and uses different arguments.
Ah, I get it now. The CONVERT function for one implementation of SQL is not the same as another - that certainly adds to the layers of confusion!
Thank you for pointing me toward the Code Snippets section; I was able to browse the functions in “SQL for Oracle” to find CONVERT, finding out that its implementation is not anywhere similar to that explained above. Instead, the TO_CHAR(datetime) function seems to be the one to use for me. I am playing with it now and will report back with my findings.
It works! The syntax of the function is TO_CHAR(expression, format). In my case, format = ‘DD-MON-YYYY’. The Code Snippets section has a folder under the applicable database type named “Format Models” - that section explains available date/time format syntax.
Thanks, again, Debbie for your depth of knowledge and willingness to share!
