November 7, 2011 at 10:29 am
Ninja's_RGR'us (11/7/2011)
GilaMonster (11/7/2011)
Someone with more patience than I have want to finish this? http://www.sqlservercentral.com/Forums/Topic1197753-1550-1.aspxSo you're un-annoyed now? Or did we just transfer that from the op to us? :hehe:
No patience left with the OP, plenty for you guys.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2011 at 10:41 am
GilaMonster (11/7/2011)
Ninja's_RGR'us (11/7/2011)
GilaMonster (11/7/2011)
Someone with more patience than I have want to finish this? http://www.sqlservercentral.com/Forums/Topic1197753-1550-1.aspxSo you're un-annoyed now? Or did we just transfer that from the op to us? :hehe:
No patience left with the OP, plenty for you guys.
I'm sure I can make it run it out in just a couple of posts. 😀
-- Gianluca Sartori
November 7, 2011 at 10:41 am
Gianluca Sartori (11/7/2011)
Totally different topic: this thread asks how to format datetime values in T-SQL.I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).
Is anyone interested in taking a look at the draft and give some feedback?
I think the "no presentation in sql" mantra is rolled out far too often without consideration for the real situation on the ground.
If you are lucky to be far enough up the tree that you have the power to enforce good modularity, good interface design, and have got involved early enough to be effective, you may be able to insist, for example, that the datetime format at the DB interface is ISO 8601 (or ODBC canonical if you prefer a space to a 'T') - or you may not; if your database is being used in conjunction for the website or a Mosque you may have to accept Hijiri format at the interface); you won't be able to insist on passing datetime2 because pretty well none of the application languages have such a type. Most of us of course are not that lucky - we arrive on the scene after wrong decisions have been taken and some of them have become irreversible (often that's why we arrive on the scene). or maybe we find that the interface definitions are being done by an incompetent who reached his/her current unassailably senior position through lateral sublimation after the Peter principle had taken her/him somewhere beyond his capabilities, and who believes that his nonsensical whims are the very gospel of interface design and modularity. Often that means we need to convert dates and times to strings in some defined format before passing them up to the application. Pretending that it doesn't happen, or that when it does happen the poor DBA should magically find a way of not doing the conversion in SQL, is just plain stupid.
Tom
November 7, 2011 at 11:34 am
L' Eomot Inversé (11/7/2011)
Gianluca Sartori (11/7/2011)
Totally different topic: this thread asks how to format datetime values in T-SQL.I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).
Is anyone interested in taking a look at the draft and give some feedback?
I think the "no presentation in sql" mantra is rolled out far too often without consideration for the real situation on the ground.
If you are lucky to be far enough up the tree that you have the power to enforce good modularity, good interface design, and have got involved early enough to be effective, you may be able to insist, for example, that the datetime format at the DB interface is ISO 8601 (or ODBC canonical if you prefer a space to a 'T') - or you may not; if your database is being used in conjunction for the website or a Mosque you may have to accept Hijiri format at the interface); you won't be able to insist on passing datetime2 because pretty well none of the application languages have such a type. Most of us of course are not that lucky - we arrive on the scene after wrong decisions have been taken and some of them have become irreversible (often that's why we arrive on the scene). or maybe we find that the interface definitions are being done by an incompetent who reached his/her current unassailably senior position through lateral sublimation after the Peter principle had taken her/him somewhere beyond his capabilities, and who believes that his nonsensical whims are the very gospel of interface design and modularity. Often that means we need to convert dates and times to strings in some defined format before passing them up to the application. Pretending that it doesn't happen, or that when it does happen the poor DBA should magically find a way of not doing the conversion in SQL, is just plain stupid.
Yeah, I have a number of outside applications that I feed through files I create in SQL Server/SSIS and they all require the dates to be formatted in specific ways. My choice is either to do the formatting in SQL Server/SSIS or to write a program in-between to do that kind of formatting. I think doing that would be stupid, frankly, given that I can do the formatting perfectly well in SQL Server/SSIS.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 7, 2011 at 12:22 pm
L' Eomot Inversé (11/7/2011)
Gianluca Sartori (11/7/2011)
Totally different topic: this thread asks how to format datetime values in T-SQL.I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).
Is anyone interested in taking a look at the draft and give some feedback?
I think the "no presentation in sql" mantra is rolled out far too often without consideration for the real situation on the ground.
If you are lucky to be far enough up the tree that you have the power to enforce good modularity, good interface design, and have got involved early enough to be effective, you may be able to insist, for example, that the datetime format at the DB interface is ISO 8601 (or ODBC canonical if you prefer a space to a 'T') - or you may not; if your database is being used in conjunction for the website or a Mosque you may have to accept Hijiri format at the interface); you won't be able to insist on passing datetime2 because pretty well none of the application languages have such a type. Most of us of course are not that lucky - we arrive on the scene after wrong decisions have been taken and some of them have become irreversible (often that's why we arrive on the scene). or maybe we find that the interface definitions are being done by an incompetent who reached his/her current unassailably senior position through lateral sublimation after the Peter principle had taken her/him somewhere beyond his capabilities, and who believes that his nonsensical whims are the very gospel of interface design and modularity. Often that means we need to convert dates and times to strings in some defined format before passing them up to the application. Pretending that it doesn't happen, or that when it does happen the poor DBA should magically find a way of not doing the conversion in SQL, is just plain stupid.
as the OP said
need pass exact string format from getdate() to third party application in which I can not do anything.
The third party applicaton only take format like below(including space):
'11/07/2011 09:30:00 AM'
'12/11/2011 03:15:00 PM'
spose..."it all depends" 😉
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 7, 2011 at 12:31 pm
L' Eomot Inversé (11/7/2011)
Gianluca Sartori (11/7/2011)
Totally different topic: this thread asks how to format datetime values in T-SQL.I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).
Is anyone interested in taking a look at the draft and give some feedback?
I think the "no presentation in sql" mantra is rolled out far too often without consideration for the real situation on the ground.
If you are lucky to be far enough up the tree that you have the power to enforce good modularity, good interface design, and have got involved early enough to be effective, you may be able to insist, for example, that the datetime format at the DB interface is ISO 8601 (or ODBC canonical if you prefer a space to a 'T') - or you may not; if your database is being used in conjunction for the website or a Mosque you may have to accept Hijiri format at the interface); you won't be able to insist on passing datetime2 because pretty well none of the application languages have such a type. Most of us of course are not that lucky - we arrive on the scene after wrong decisions have been taken and some of them have become irreversible (often that's why we arrive on the scene). or maybe we find that the interface definitions are being done by an incompetent who reached his/her current unassailably senior position through lateral sublimation after the Peter principle had taken her/him somewhere beyond his capabilities, and who believes that his nonsensical whims are the very gospel of interface design and modularity. Often that means we need to convert dates and times to strings in some defined format before passing them up to the application. Pretending that it doesn't happen, or that when it does happen the poor DBA should magically find a way of not doing the conversion in SQL, is just plain stupid.
Thank you for saying this.
-Ki
November 7, 2011 at 12:42 pm
L' Eomot Inversé (11/7/2011)
Gianluca Sartori (11/7/2011)
Totally different topic: this thread asks how to format datetime values in T-SQL.I think this is a very bad habit to kick and I put together an article (well, it was meant to be a post for my blog, but I'm sure it would have a larger audience here).
Is anyone interested in taking a look at the draft and give some feedback?
I think the "no presentation in sql" mantra is rolled out far too often without consideration for the real situation on the ground.
If you are lucky to be far enough up the tree that you have the power to enforce good modularity, good interface design, and have got involved early enough to be effective, you may be able to insist, for example, that the datetime format at the DB interface is ISO 8601 (or ODBC canonical if you prefer a space to a 'T') - or you may not; if your database is being used in conjunction for the website or a Mosque you may have to accept Hijiri format at the interface); you won't be able to insist on passing datetime2 because pretty well none of the application languages have such a type. Most of us of course are not that lucky - we arrive on the scene after wrong decisions have been taken and some of them have become irreversible (often that's why we arrive on the scene). or maybe we find that the interface definitions are being done by an incompetent who reached his/her current unassailably senior position through lateral sublimation after the Peter principle had taken her/him somewhere beyond his capabilities, and who believes that his nonsensical whims are the very gospel of interface design and modularity. Often that means we need to convert dates and times to strings in some defined format before passing them up to the application. Pretending that it doesn't happen, or that when it does happen the poor DBA should magically find a way of not doing the conversion in SQL, is just plain stupid.
I don't think Gianluca is saying that you don't do what you have to do, but by writing an article/blog post about it, he will be trying to educate so that people learn that the database isn't the place to do presentation.
I'm supporting an app that does conversions of date parameters to a specific format, just to put them into a DateTime/SmallDateTime column, so SQL Server doesn't need the conversion. This was a port from Oracle and never having had the pleasure of supporting Oracle, I don't know if this is different there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 7, 2011 at 5:12 pm
Tom, you made a very good point and it would be great if you gave me the permission to include your considerations in my article.
Indeed, there are good reasons to do the formatting in SQL, but many developers just do it because it comes quicker, not because they are forced by an unfortunate fate. My intent is to stress the negative impacts of storing and passing string representations of dates instead of actual dates but also to enumerate all the built-in conversion methods that I'm aware of, plus a couple of custom T-SQL functions that I wrote for this very purpose.
In the end, I live in the real world and the main DB I'm working on now contains all the evil you can think of. A date stored as string is nothing compared to the sins I have to fight on a daily basis. 🙂
After all, you can be right even if you say the same things as Celko. 😛
-- Gianluca Sartori
November 7, 2011 at 5:17 pm
BTW, I must be very low on that tree, because I was never able to make my point loud enough as to have all the sins I mentioned above fixed.
I know what you're talking about, unfortunately.
-- Gianluca Sartori
November 8, 2011 at 6:12 am
Hey everyone, I am hijacking the thread with an "Off Topic"
I am planning on a presentation for my user group "DBA MythBusters" (Courtesy Paul Randal) His idea is brilliant. It is a fun filled session. I would like to do one like that for our user group. If anybody have suggestions on what Myth to bust please, please let me know. The audience is 100 to 200 level.
-Roy
November 8, 2011 at 6:21 am
Roy Ernest (11/8/2011)
Hey everyone, I am hijacking the thread with an "Off Topic"I am planning on a presentation for my user group "DBA MythBusters" (Courtesy Paul Randal) His idea is brilliant. It is a fun filled session. I would like to do one like that for our user group. If anybody have suggestions on what Myth to bust please, please let me know. The audience is 100 to 200 level.
Backing up the server drives backs up your databases.
Full Backup takes care of your transaction log.
Security issues (RE: GRANT gives you everything, even if you have DENY through another group/login)
You do not have to check your Jobs Monitor because alerts and notifications (even the ones you forgot to set up) will tell you when things failed.
Backups? We don't need no stickin' backups!
...I'm sure there are others, but those are the ones I can think of right now.
November 8, 2011 at 6:22 am
Roy Ernest (11/8/2011)
Hey everyone, I am hijacking the thread with an "Off Topic"
:w00t: How dare you!
Roy Ernest (11/8/2011)
I am planning on a presentation for my user group "DBA MythBusters" (Courtesy Paul Randal) His idea is brilliant. It is a fun filled session. I would like to do one like that for our user group. If anybody have suggestions on what Myth to bust please, please let me know. The audience is 100 to 200 level.
Maybe the biggest one of them all:
TRUNCATE is not logged and cannot be rolled back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 8, 2011 at 6:26 am
Roy Ernest (11/8/2011)
Hey everyone, I am hijacking the thread with an "Off Topic"I am planning on a presentation for my user group "DBA MythBusters" (Courtesy Paul Randal) His idea is brilliant. It is a fun filled session. I would like to do one like that for our user group. If anybody have suggestions on what Myth to bust please, please let me know. The audience is 100 to 200 level.
Table variables are memory-only and temp tables are not
Stored procedure plans are cached, statements plans are not
-- Gianluca Sartori
November 8, 2011 at 6:35 am
Those are some interesting thoughts I can add. I have already made a not of them 🙂 Thx, if you think of more, please keep it coming.
-Roy
November 8, 2011 at 6:38 am
Clustered indexes enforce physical sort order of rows
Nonclustered indexes can't be built on a heap
Nonclustered indexes on a heap can't be rebuilt
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 31,471 through 31,485 (of 66,749 total)
You must be logged in to reply to this topic. Login to reply