Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

DateTime Trick Expand / Collapse
Author
Message
Posted Tuesday, October 27, 2009 4:16 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, Visits: 373
Interesting..

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
Post #809179
Posted Tuesday, October 27, 2009 4:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:31 AM
Points: 363, Visits: 1,317
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...
Post #809193
Posted Tuesday, October 27, 2009 6:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 3,998, Visits: 6,053
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? -- Stephen Stills
Post #809245
Posted Tuesday, October 27, 2009 6:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #809253
Posted Tuesday, October 27, 2009 6:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:31 AM
Points: 363, Visits: 1,317
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...
Post #809256
Posted Tuesday, October 27, 2009 7:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 3,998, Visits: 6,053
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? -- Stephen Stills
Post #809282
Posted Tuesday, October 27, 2009 7:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:31 AM
Points: 363, Visits: 1,317
Bob - at the end of the article there is a link to another post containing some "pros" for using CONFERT(), etc...
Post #809300
Posted Tuesday, October 27, 2009 8:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 3,998, Visits: 6,053
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? -- Stephen Stills
Post #809329
Posted Tuesday, October 27, 2009 8:29 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #809354
Posted Tuesday, October 27, 2009 10:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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?

Post #809434
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse