DateFormat

Question

DateFormat

vshah555

SSC Eights!

Points: 949
More actions
July 2, 2012 at 10:24 pm

#258287

Comments posted to this topic are about the item DateFormat
July 3, 2012 at 3:03 am

This was removed by the editor as SPAM
July 3, 2012 at 3:06 am

This was removed by the editor as SPAM

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 2

Good question. Is this related to the behaviour of datetimes versus datetime2s with the british language setting?

SET LANGUAGE BRITISH

GO

DECLARE @datevar datetime2 = '2008-12-13'; -- no error, ymd format

SELECT @datevar;

GO

DECLARE @datevar datetime = '2008-12-13'; -- error, ydm format

SELECT @datevar;

GO

I can't find a specific reference, but it looks as though SET LANGUAGE BRITISH implicitly sets the dateformat to ydm for datetimes and smalldatetimes and to ymd for dates and datetime2s.

rodjkidd SSCoach Points: 15915 More actions · Answer 3

Nice question.

Can to the conclusion it wouldn't with with offset, then it was a case of old datetime types vs new or all?

I guessed wrong. But one to put in the meory banks for future use.

Cheers,

Rodders...

Richard Warr SSCertifiable Points: 6966 More actions · Answer 4

Well, I'm British and I don't know anybody who has ever used YDM format. We go for DMY or YMD.

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 5

Richard Warr (7/3/2012)
Well, I'm British and I don't know anybody who has ever used YDM format. We go for DMY or YMD.

I do, but it has been "by accident" in that if a server/database/connection/whatever is set up as british (as opposed to us-english) then using yyyy-mm-dd will have unexpected results since for datetimes and smalldatetimes it is interpreted as yyyy-dd-mm. It's why I always insist people use yyyymmdd format for dates.

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 6

Stewart "Arturius" Campbell (7/3/2012)
Duncan Pryde (7/3/2012)
Good question. Is this related to the behaviour of datetimes versus datetime2s with the british language setting?
SET LANGUAGE BRITISH
GO
DECLARE @datevar datetime2 = '2008-12-13'; -- no error, ymd format
SELECT @datevar;
GO
DECLARE @datevar datetime = '2008-12-13'; -- error, ydm format
SELECT @datevar;
GO
I can't find a specific reference, but it looks as though SET LANGUAGE BRITISH implicitly sets the dateformat to ydm for datetimes and smalldatetimes and to ymd for dates and datetime2s.
Try sp_helplanguage @language = 'BRITISH' and check the dateformat column

Thanks for the tip. I realised that the default format for BRITISH would be dmy after I posted, and running your code confirms that. However, when dealing with dates like '2008-04-05', BRITISH interprets it as 4th May 2008, not 5th April 2008 - i.e. as ydm rather than ymd. Again though, I can't remember where that behaviour is documented.

vshah555 SSC Eights! Points: 949 More actions · Answer 7

vshah555

SSC Eights!

Points: 949

July 3, 2012 at 4:22 am

#1508131

Thanks all for the feedback

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Thanks for the question. I thought it was bad enough being in America where most everyone uses mmddyyyy. But, yyyyddmm makes even less sense to me. Anyone know why thats used?

Also, how about another new function:

sp_helpdriver @driver = N'British'

Result = 'Left side of road'

:hehe:

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 9

Thomas Abraham (7/3/2012)
Thanks for the question. I thought it was bad enough being in America where most everyone uses mmddyyyy. But, yyyyddmm makes even less sense to me. Anyone know why thats used?

There might be someone who does, but this thread from someone trying to find out makes entertaining reading (he never got his answer): http://social.msdn.microsoft.com/Forums/en-AU/sqldatabaseengine/thread/04d9917d-fc05-4e3e-ba70-0e8a3eac3fe8

Thomas Abraham (7/3/2012)
Also, how about another new function:
sp_helpdriver @driver = N'British'
Result = 'Left side of road'
:hehe:

It could have other parameters

sp_helpdriver @driver = N'British', @type=N'Taxi'

Result = 'Pretty much anywhere he pleases'

Dana Medley SSCertifiable Points: 6764 More actions · Answer 10

Excellent question. Thank you for that and Duncan thank you for that entertaining read. Poor guy, I kinda felt sorry for him at the end. I guess this is just gonna get put into that category of "The answer is 42". :hehe:

Everything is awesome!

Thomas Abraham SSChampion Points: 10761 More actions · Answer 11

Duncan Pryde (7/3/2012)
There might be someone who does, but this thread from someone trying to find out makes entertaining reading (he never got his answer): http://social.msdn.microsoft.com/Forums/en-AU/sqldatabaseengine/thread/04d9917d-fc05-4e3e-ba70-0e8a3eac3fe8

Thanks for the link. I too felt sorry for the guy. Caused me to go on a hunt for a better answer. Found a few similar posts, but no one seems to have an answer. It does appear to be an artifact of an attempt at backwards compatibility with perhaps undesirable consequences. I came across this, which almost sheds light on the subject: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=290971

Shows that in 2007 someone at Microsoft thought that:

1. YYYY-DD-MM should be deprecated.

2. THey knew that no culture actually used that format.

And now, back to work that pays, little as that may be.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

EL Jerry SSCertifiable Points: 7063 More actions · Answer 12

Thank you for the question.

I also agree that YYYYDDMM is rarely used anywhere, and I use YYYYMMDD as standard, as it is easier to interpret in any situation.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]