Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Volkan Kuyucuoglu » DateTime Trick
Add to briefcase
20 posts, Page 1 of 2
12»»

DateTime Trick Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
VOLKAN KUYUCUOGLU-450865
Posted Saturday, October 24, 2009 1:21 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, December 03, 2012 2:34 AM
Points: 563, Visits: 27
Comments posted to this topic are about the item DateTime Trick
Post #808331
The Dixie Flatline
Posted Sunday, October 25, 2009 9:04 PM


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 @ 1:24 PM
Points: 3,836, Visits: 5,634
Good question and interesting result. I'm surprised that it accepted mixed dots and dashes as separators in the date. I thought it would break if I changed it from 114 to 112. Thanks for this one!

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #808451
john.arnott
Posted Sunday, October 25, 2009 11:23 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010
Bob Hovious 24601 (10/25/2009)
Good question and interesting result. I'm surprised that it accepted mixed dots and dashes as separators in the date. I thought it would break if I changed it from 114 to 112. Thanks for this one!

I agree -- interesting result. The real trick here was not mentioned in the explanation. The mixed delimiters would indeed break the convert to varchar if we were relying on an implicit conversion from string to datetime, but are handled OK by the explicit Convert(datetime,'2009/01.01').
Post #808493
honza.mf
Posted Monday, October 26, 2009 3:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 1:11 AM
Points: 1,114, Visits: 1,209
First time I consulted BOL before answering. Who remembers all those datetime styles?


See, understand, learn, try, use efficient
© Dr.Plch
Post #808561
Andrew Watson-478275
Posted Monday, October 26, 2009 4:49 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: Monday, June 03, 2013 6:20 AM
Points: 954, Visits: 1,878
honza.mf (10/26/2009)
First time I consulted BOL before answering. Who remembers all those datetime styles?

I'm glad you admitted this too. I always feel a bit guilty when I sneak a peek at BOL before answering, but probably justified in this case.
Post #808574
honza.mf
Posted Monday, October 26, 2009 5:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 1:11 AM
Points: 1,114, Visits: 1,209
Andrew Watson-478275 (10/26/2009)
honza.mf (10/26/2009)
First time I consulted BOL before answering. Who remembers all those datetime styles?

I'm glad you admitted this too. I always feel a bit guilty when I sneak a peek at BOL before answering, but probably justified in this case.

I think QOD are a kind of a game. One can find an answer in BOL or even copy-paste the script to query analyzer, but it doesn't give me the good feeling. I prefer many bad answers.
Today is an exception. I don't have phone book in my head and I don't want to have.


See, understand, learn, try, use efficient
© Dr.Plch
Post #808587
Rachel Byford
Posted Monday, October 26, 2009 5:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:34 AM
Points: 1,544, Visits: 816
The BOL page with the meanings of the formats is the only one I keep in my favourites.
Post #808602
SanjayAttray
Posted Monday, October 26, 2009 8:41 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: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
Does any one uses mixed datetime styles in a single query like the above one ?

I haven't seen any.

SQL DBA.
Post #808739
Tom Garth
Posted Monday, October 26, 2009 1:54 PM


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 04, 2011 7:20 AM
Points: 977, Visits: 1,499
SanjayAttray (10/26/2009)
Does any one uses mixed datetime styles in a single query like the above one ?

I haven't seen any.


Probably not, but some readers no doubt will benefit from knowing the CONVERT function exists and what it can do. It's a good tool to use in reporting or on inquiry forms. I get a lot of use from:
SELECT CONVERT(varchar(10),GETDATE(),126)

.
More often with a Datetime column than with GETDATE(). Note the varchar length to drop the time element.

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 #808955
Toreador
Posted Tuesday, October 27, 2009 4:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 1,383, Visits: 4,870
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.
Post #809175
« Prev Topic | Next Topic »

Add to briefcase
20 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.