|
|
|
SSChampion
Group: Administrators
Last Login: 2 days ago @ 8:26 PM
Points: 23,166,
Visits: 6,925
|
|
|
|
|
|
SSCrazy Eights
Group: Moderators
Last Login: Friday, January 22, 2010 10:24 AM
Points: 8,307,
Visits: 521
|
|
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)
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Saturday, January 22, 2005 9:30 AM
Points: 3,
Visits: 1
|
|
I agree with Antares686. MS needs to address the limitations with CAST. Having said that, I do use CAST a lot.
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: 2 days ago @ 8:26 PM
Points: 23,166,
Visits: 6,925
|
|
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
steve@dkranch.net
|
|
|
|
|
SSCrazy Eights
Group: Moderators
Last Login: Friday, January 22, 2010 10:24 AM
Points: 8,307,
Visits: 521
|
|
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)
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Wednesday, February 19, 2003 12:00 AM
Points: 14,
Visits: 1
|
|
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.
|
|
|
|
|
SSCrazy Eights
Group: Moderators
Last Login: Friday, January 22, 2010 10:24 AM
Points: 8,307,
Visits: 521
|
|
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)
|
|
|
|
