March 10, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/castyourdata.asp
March 10, 2002 at 7:44 am
Simple enough on using and why. However here I really don't agree.
quote:
Also, as more SQL Server databases grow, there will be more and more DBAs that come from other products to work with SQL Server. These DBAs will likely write SQL code that is more SQL-92 standard and will not include the T-SQL legacy code words.
The reason is cast still has too many limitations to it with formating (style) which need to be addressed. For example many Oracle DBAs use to_char and to_date where they can write to_date(dateFld,'mm/dd/yyyy hh:mmPM') to generate a specific style. Also products similar to Oracle have not to my knowledge adopted CAST as of yet. So what, IMHO, you will see is that the first time they have to use CONVERT to reach a goal and realize that it will work in all cases (even if they loss some persision) they will tend towards it. I had in the past been real bad about using CONVERT and only recently (last year or so) broke myself of it.
I do agree that ultimately the goal should be move to CAST but there needs to either be a style of available so we can just drop convert or it may be hard coming to get everyone, no matter what.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 18, 2002 at 10:40 am
I agree with Antares686. MS needs to address the limitations with CAST. Having said that, I do use CAST a lot.
March 18, 2002 at 12:09 pm
To me, dates are the only real issue. I wish they had included something in cast, even an exension, to handle dates, but I guess that's more of a standards issue.
I still think you should use CAST wherever possible. Formatting will still need convert.
Steve Jones
March 18, 2002 at 1:07 pm
Actually Steve today I may have found a reason to rethink the whole convert thing. I have been adamant myself on CONVERT(VARCHAR,DATEVAL,101) as opposed to the whole cast thing due to memory processes. But today I decided to try profiler to see what else goes on and discovered something I never realized before. Convert with a style calls directly to the database (if fact I saw 2 reads every time) tables in master so even though in memory Convert is better Cast doesn't need to make these extra reads and if the server is suffering from a high number of reads then you are now adding to those. I am working on an idea and trying to see if I can figure out which has the actual lowest overhead between the two. Seems odd though that Microsoft did not hard code the styles into convert itself, preventing this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 18, 2002 at 7:26 pm
Using Cast comes unstuck when you want to use a GUI tool to look at your SQL Queries. I often use Visual Interdev to look at my SQL Queries because it's easy to use and does not require you to save queries before you run them (as Access does) but InterDev does not allow AS to be used in SQL Queries - don't ask me why - I don't know. But it means that I need to change all the CAST's to CONVERT's just to look at my SQL in a GUI tool, so forget it, I will always use convert.
Also to Atares686, I hope your database is not being used anywhere outside the US, or you will have a lot of changing hard coded date formatting values if you want anyone else in the world to look at your data. We have a policy here of only letting the user interface format dates (our DB's are in AUS but are used in the US), then it can be displayed to the format of the user.
March 18, 2002 at 7:33 pm
How are you accessing the database (ADO, ODBC), I have had very little issue with using AS in my queries thru ADO with SQLOLEDB.1 as provider.
Also as for formatting I don't deal with international business units in a capacity that I need to worry about dates.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply