DateTime Trick

Question

DateTime Trick

VOLKAN KUYUCUOGLU-450865

Mr or Mrs. 500

Points: 573
More actions
October 24, 2009 at 1:21 pm

#216566

Comments posted to this topic are about the item DateTime Trick

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

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? Everybody look what's going down. -- Stephen Stills

john.arnott SSChampion Points: 11882 More actions · Answer 2

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').

honza.mf SSCertifiable Points: 5519 More actions · Answer 3

First time I consulted BOL before answering. Who remembers all those datetime styles?

See, understand, learn, try, use efficient
© Dr.Plch

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 4

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 :blush: when I sneak a peek at BOL before answering, but probably justified in this case.

honza.mf SSCertifiable Points: 5519 More actions · Answer 5

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 :blush: 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

Rachel Byford Hall of Fame Points: 3437 More actions · Answer 6

The BOL page with the meanings of the formats is the only one I keep in my favourites.

SanjayAttray SSChampion Points: 13157 More actions · Answer 7

Does any one uses mixed datetime styles in a single query like the above one ?

I haven't seen any.

SQL DBA.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 8

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[/url]

"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

Toreador SSChampion Points: 11368 More actions · Answer 9

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.

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 10

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 SSCertifiable Points: 6334 More actions · Answer 11

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 SSC Guru Points: 53354 More actions · Answer 12

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 SSCertifiable Points: 6193 More actions · Answer 13

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[/url]

"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 SSCertifiable Points: 6334 More actions · Answer 14

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...