5 ms·
Now if only ISO 8601 date formatting would catch on... (yyyy-mm-dd)
by jeffool 15y ago
Now if only ISO 8601 date formatting would catch on...
(yyyy-mm-dd)
- dspillett 15y agoI'm with you on that. If only MSSQL Server wasn't broken in the way it interprets that if your user locale is set to UK English (give it a string formatted that way in other locales and the behaviour is as expected, in "English (British)" it sees the format as yyyy-dd-mm).
- eftpotrm 15y agoI'm British, I've worked with a large number of clients' SQL Server installs. I think I've only twice seem yyyy-dd-mm as a default behaviour, yyyy-mm-dd was far more common.
- dspillett 15y agoI've only seen it assume yyyy-dd-mm when the user's locale is set to "English (British)". In most instances I've come across this setting is just left at the default ("English", which implies "English (American)") even if everything else in the stack is localised for the UK, so the oddity isn't noticed. My build instructions now explicitly state that "English (British)" should not be used due to the way it affects how dates in strings are interpreted when the format NNNN-NN-NN is seen.
- eftpotrm 15y agoOdd. From memory, the safest format to use is actually yyyymmdd (without the hyphens) though, which is apparently supposed to be interpreted unambiguously by SQL Server.
- hessenwolf 15y agoAh, the old 112 instead of 120 format.
- eftpotrm 15y agoY'know, I got so fed up with memorising those numbers at an old job that I built a view that enumerated all the valid ones against GETDATE() :-)
- hessenwolf 15y agoI have a formatDate() function lying around somewhere, but I cannot create functions or views on many of the DBs. convert(varchar(8), @myDate, 112) seems a daft way to go about things - and I have been wondering if I have been doing something wrong...
- eftpotrm 15y agoAgreed. The CONVERT syntax is rubbish, but the output is so much more powerful. I'd rather have an equivalent of Date.ToString("YYYY-mmm-DD hh:mi:ss.ssss") in SQL but, sadly.... It could be written as a UDF but I'm not sure performance would be up to production use. I had a wonderful stack of little helper functions and views I'd cobbled together at that job and had had free rein to stick into the real databases; they made development so much easier. The downside of employment though, that sort of work gets stuck with each employer you do it for and can't so easily be carried around like a toolkit. I experimented a bit with some of the code from http://www.simple-talk.com/community/blogs/philfactor/archive/2010/12/10/96089.aspx http://www.simple-talk.com/community/blogs/philfactor/archiv... a while back on some toy projects; it makes some interesting reading and had me building a few similar functions around my own conventions and rules. On a larger scale, I would be sorely tempted to build something like that into the Model database and insist on a clean output as part of the approval criteria.
- pradocchia 15y agoIt could be written as a UDF but I'm not sure performance would be up to production use. If you ever find yourself in that situation again, write the UDF as a in-line table-valued function. You get multiple return values and avoid the scalar UDF hit, since the engine will execute it in-line (appropriately enough). eg: select t.date, d.date_as_string from table1 t cross apply dbo.format_date(t.date,'yyyy-mm-dd') d
- pradocchia 15y agoyyyymmdd is the only safe format on MSSQL.
- furyg3 15y agoAlso: 24h time with no timezones.
- unwind 15y agoWhy no timezones? Do you propose that all times always be given as 24-hour UTC?
- furyg3 15y agoExactly. With international communication on the rise, it's best (IMHO) to do away with the timezone concept, and just use one time format everywhere. India is +5:30, Nepal is +5:45, California observes DST but Arizona doesn't... the EU does, but they start and end DST a week before the US... It's ridiculous. The initial gut reaction against this is that people would hate waking up at 18:00 and going home at 8:00, but I think people would get used to this pretty quickly. After all, we do this twice a year as it is.
- Djehngo 15y agoConsider also that there is one hour per year which does not exist and one hour per year which happens twice (at-least within the context of local time if you live in an area with daylight savings) There is also Namibia which used to lose hours in daylight savings time and Bangladesh which had dst in 2009 then dropped it, this makes historical time calculations tricky.
- bonzoesc 15y agoTimes should really be given as Miami Internet Beats[1], since it's compatible with everyone in US Eastern and doesn't have antiquated concepts like "hours," or "minutes." [1]: http://miamibeats.org/ http://miamibeats.org/
- jeffool 15y agoI agree with this. yyyy.mm.dd.hh.mm.ss Also? The Holocene calendar, and the International Fixed Calendar. But then, I'm a nerd.
- Derbasti 15y agoHow about going metric?
- knshaum 15y agoCharles Stross does that in his novel Glasshouse: durations are described in kiloseconds, megaseconds, and gigaseconds. (100Ksec is just under 28 hours -- a reasonable proxy for a day -- and 1 Gsec is about 31 years.) In a space-faring society, with no standard astronomical day or year, it made sense.
- henrikschroder 15y agoIt's the national standard in my country! (Then again, it's the national standard of two whole countries, everyone else ignores it. :-( )
- rimantas 15y agoThe same in mine. I had no idea it was so rare :(
- EdiX 15y agoOne is china, I think. Which is the other one?
- henrikschroder 15y agoLast time I checked it was Sweden and Japan. Dunno if more countries have seen the light since. :-)
- angus77 15y agoThat's the way it's not only written, but spoken in Japanese.
- Sukotto 15y agoJapan uses three different calendars, depending on context. Imperial / kōki which counts from alleged founding of Japan in 660BC Era / nengō which counts the years from the start of the Emperor's reign European / seireki adopting the International standard date counting from the alleged birth date of Jesus Christ Today is 平成23年5月13日 of Mikado Heisei, the 125th Emperor of Japan
- angus77 15y agoIn all three systems, it is both written and spoken year-month-day, which was the point. Kōki is not used by anyone with a straight face in this day and age. "Mikado Heisei" is an extremely uncommon way to refer to the emperor. "帝平成" gets 724 hits on Google, and "Mikado Heisei" gets 6, in 5 different languages---the first of which is your post above. Even the Japanese-language wikipedia page on him doesn't use the word "帝" anywhere.
- run4yourlives 15y agoYes please! I'm sick and tired of people arguing that you put the month first because "That's how you say it".
- hugh3 15y agoOf course you only say it that way in America. Today is the Thirteenth of May, not May 13. I prefer dd/mm/yyyy to yyyy/mm/dd though, just to keep what is usually the most important information at the front.
- Pistos2 15y agoI like YYYY-MM-DD for two reasons: 1) Sorting the string representations sorts in time order. 2) It eliminates ambiguity almost completely, because extremely few places use YYYY-DD-MM. The ambiguity of most ##/##/## dates is irritating.
- run4yourlives 15y agodd-mm-yyyy can easily be confused with mm-dd-yyyy. The only format that is obvious is yyyy-mm-dd.
- hugh3 15y agoUntil somebody starts using yyyy-dd-mm and screws everything up. Besides, often when you write dates you leave the year off entirely.
- r00fus 15y agoI prefer how we did it in the military: 13MAY2011. mysql: %d%b%Y No ambiguity, easy to parse and 3-letter month is natural delimiter between day and year numbers.