SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DateTime Trick


DateTime Trick

Author
Message
Kari Suresh
Kari Suresh
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 373
Interesting..w00t

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1526 Visits: 1479
It's useful to try to answer to such questions...:-)
You may find out that
SELECT CONVERT(DATETIME,'2009-01.01') and
SELECT CONVERT(DATETIME,'2009-01-01')
both work...:-)
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12570 Visits: 6903
Toreador (10/27/2009)
Surely date formatting should normally be part of the presentation layer? I always return the whole datetime value and format it on the client, using their regional settings etc.


Good point, but CONVERT still comes in handy for bringing in data from an outside system, exporting a batch of data to an older system that uses YYYYMMDD, etc. It's also useful for converting strings to dates when the strings are in a format that T-SQL won't implicitly convert.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Tom Garth
Tom Garth
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1951 Visits: 1499
Toreador (10/27/2009)
Surely date formatting should normally be part of the presentation layer? I always return the whole datetime value and format it on the client, using their regional settings etc.


I don't know that to be true. I've never heard that statement before. If I'm writing the code for the dataset as well as formatting the presentation layer. Then why shouldn't I use the fastest route for getting the results. Conversions, trimming, and even column concatenation are all methods that are more easily implementd with T-SQL than with some reporting applications.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1526 Visits: 1479
Bob, Tom - please see http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/02/19/sql-tip-keep-your-presentation-logic-in-your-presentation-layer.aspx...
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12570 Visits: 6903
dmoldovan (10/27/2009)
Bob, Tom - please see http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/02/19/sql-tip-keep-your-presentation-logic-in-your-presentation-layer.aspx...


Let me be clear, my comments about the usefulness of convert are in the context of doing an import data task, or a one-off query to go to another system. For example, I occasionally pull data that feeds some SAS code for one of our statistics gurus. He asks me to convert dates to YYYYMMDD.

Now, I am absolutely sure that I waste a few cycles doing this with a CONVERT() in my query. But the system is under no CPU stress, and the alternative is to either refuse his request, requiring him to figure out how to handle native data formats on his side and go through all his code making changes... or to learn enough SAS to do it myself. That is a waste of valuable man hours to keep an insignificant load off the CPU. Those man-hours could be better spent on projects that would REALLY make a difference to performance, or to my company's revenue. In this case, common sense dictates that I sacrifice a little machine resource to save a lot of people resource.

Machines are still meant to serve man.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1526 Visits: 1479
Bob - at the end of the article there is a link to another post containing some "pros" for using CONFERT(), etc...
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12570 Visits: 6903
My apologies for not reading it in the entirety. I will go back and do that now. Thanks. :-)

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Tom Garth
Tom Garth
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1951 Visits: 1499
dmoldovan (10/27/2009)
Bob, Tom - please see http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/02/19/sql-tip-keep-your-presentation-logic-in-your-presentation-layer.aspx...


Both articles make good points, but note that I did say with some reporting applications. For instance it would be wasted time with a Crystal report.

I think that the original blog picked an example that doesn't really line up with the conversation that we're having, but his opinion is valid never the less, except the part about the T-SQL example being more difficult to read than C# or VB. That would definitely be an 'in the eye of the beholder' opinion.

As for performance. That is always a consideration, but tweaking it to the max can be a luxury item with some jobs. For instance with a medium sized company needing a custom report from an over-powered, under-utilized database server. I don't work with Enterprise level projects every day, but my time still counts, and ultimately giving the client the most bang for his buck is what we are measured by.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3536 Visits: 3059
There's been a lot of interesting discussion here, but now I'm curious what Volkan, the author of this QOD, had in mind. Was it to show that CONVERT could isolate the time portion of a datetime? The explanation given would imply that. Or was the use of mixed delimiters the "trick" to which the title referred?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search