 Notice. New forum software under development. It's going to miss a few functions and look a bit ugly for a while, but I'm working on it full time now as the old forum was too unstable. Couple days, all good. If you notice any issues, please contact me. |
Forum Index : Off topic archive. : American Date Format!
| Author | Message | ||||
Gizmo Admin Group  Joined: 05/06/2004 Location: AustraliaPosts: 5036 |
The americans use a funny date format of MM/DD/YYYY, while just about everywhere else uses a DD/MM/YYYY format. Now for the average person this isn't a problem, but for anyone who has to write software it is a nightmare. There are two common databases systems used in software, Access and SQL. If I need to record date information into a database, the database assumes an american format, even if your are in another country. ie 5th April, 2005 = 5/4/2005 in Australia, 4/5/2005 in USA. So if I insert this date, 5/4/2006, and then later ask for the date, it comes out as 4/5/2006 (4th May 2005)! But if I insert 15th April, 2005 ( 15/4/2005 ) it will come out as 15th April 2005! So the database is taking a guess! This is a crazy setup, it would be better if the database returned a error. Unfortunately there is no way to configure the databases to use a specific format. One solution to overcome the problem is to insert "5 April 2005" instead of "5/4/2005", and this will give the correct response, usually. But some databases get upset if you insert a long date instead of the expected short "MM/DD/YYYY" format. As progrrammers we use this rule. The database returns the date in the format of the country of use, but inserts dates in USA format. So we insert dates in the USA format, and the database will return the date in my countries format. There have been thousands of posts about this problem on programming web sites, and no real answer. Many agree it was a silly approach by Microsoft use use the american MM/DD/YYYY date format, when most of the planet uses DD/MM/YYYY. I use several routines in my code to check date formats when using databases, but it still has the occasional hickup. Just the other day an employee entered some hours to a job on the 9th June, but later when the job was been totaled, it was short a few hours, we later found them on the 6th September! There is a push by programmers to use the international standard of YYYY/MM/DD and I think its a good idea, but we need Microsoft to get behind it and change their database servers. The best time to plant a tree was twenty years ago, the second best time is right now. JAQ |
||||
| Gizmo Admin Group Joined: 05/06/2004 Location: AustraliaPosts: 5036 |
Another little problem, but this is caused by the people who write printer drivers. USA uses a standard paper size called "Letter", but we use a size called "A4". A4 is a little wider and shorter than Letter. All printer drivers have A4 and Letter as options, so we just select A4 as the default paper. But some poorly written printer drivers keep switching back to the default size of "Letter". So every time you print you need to change the paper size back to A4! Arghh. This is a big problem on network printers, if someone acidentally prints to "Letter", the printer will stop processing and wait for someone to load some Letter sized paper into the trays, while about 100 plus print jobs que up behind it. The best time to plant a tree was twenty years ago, the second best time is right now. JAQ |
||||
| RossW Guru  Joined: 25/02/2006 Location: AustraliaPosts: 495 |
Giz: the answer to your problem is to NOT STORE THE DATE. Whichever IDIOT decided we had to store dates in this form AT ALL, needs taken out and SHOT. Those of us who have to store LOTS of data, and recover it and DO THINGS WITH IT don't use either format. (Another nasty trick you didn't mention is daylight savings transitions, but thats another can of worms). Many people in the data-manipulation world choose to store dates in the form YYYY/MM/DD for the simple reason that it's easy to sort. Those of us who have quite a bit more data to process use a strange animal called "UNIXTIME". It's a long integer representing the number of seconds since the epoch (00:00 on 1/Jan/1970). Storing a single, 32-bit number gives you not only the date, but the time. It's easy to convert into any representation you like (most languages support either a unixtime conversion to/from a structure, or at least a formatted string). $ date +%s 1152255306 $ date -r 1152255306 +"%Y/%b/%d %H:%M:%S" 2006/Jul/07 16:55:06 $ date -r 1152255306 +"%Y/%m/%d %H:%M:%S" 2006/07/07 16:55:06 $ date -r 1152255306 +"%d/%m/%y" 07/07/06 $ date -r 1152255306 +"%a %d/%m/%y" Fri 07/07/06 Damn, I would pick a day where month=day! Lets whack off a day (86,400 seconds) $ date -r $(( 1152255306 - 86400 )) +"%a %d/%m/%y" Thu 06/07/06 $ date -r $(( 1152255306 - 86400 )) +"%a %m/%d/%y" Thu 07/06/06 I have been storing weather records (16 environment variables) every minute for the last 22 years, and can quickly and easily find data for any particular day, or dates and times on which (say) the temperature was >38 degrees and humidity <20% and windgust >20 KMH, then display the data to the day/month/year hour/minute/second from an integer. In business, we record every call (about 100,000 per month) and provide real-time on-line information to customers about them. With call times in this form, a stop time and a duration let us work out anything we want and display it quickly and easily. Have a look. If you can do it, it'll make your life easy! |
||||
| Gizmo Admin Group Joined: 05/06/2004 Location: AustraliaPosts: 5036 |
Thanks Ross, thats a clever way of storing dates, I'll keep it in mind. One of my problems is I write software for a large system, used by 120 people. Very difficult to find a way to enter dates and keep everyone happy. Office girls want DDMMYYYY because its faster to type, floor staff want DD/MM/YY ( swap the "/" for "\","-","." whatever key they find ), and I like DD Monthname YYYY cause is makes my lifer easier. And the system has records going back 15 years, in old dbase and access formats, all converted to SQL2000. We often need to search the records using a date range, easy in SQL, but SQL needs an American date format to work, and it then spits out the results in Australian format! I do like the serial date format you suggested, will look further into it. To our American viewers, dont get me wrong, there is nothing wrong with the MM/DD/YYYY format. The problem is the databases give unreliable results if asking for date information for another country. Software written for the global market must adopt international standards, not just the standards of the programmers country. The best time to plant a tree was twenty years ago, the second best time is right now. JAQ |
||||
| RossW Guru Joined: 25/02/2006 Location: AustraliaPosts: 495 |
Yes, I appreciate that! My coding is for web-based users, but I have tens of thousands of people (many of them my customers/users), who have hugely varied and diverse backgrounds and skill levels. Dropdown boxes are a favourite by lots of web-developers but they are god-aweful slow to actually use. While not infallable, my own code tries to make an intelligent guess. (Do you have "regex" in your programming environment?) The short overview of what I do, is to take the first one or two numeric characters up to but not including a non-numeric, then working BACKWARDS, take the last numeric chars up to the last non-numeric. The bit "in the middle" I strip out all characters that are not 0-9a-zA-Z and try to take as numeric. If it evaluates to 0 (ie, non-numeric) then do a case-insensitive lookup of the first 3 chars (a simple string search, find where those 3 chars appear in "Jan Feb Mar Apr May....." and divide by 4 (shift right 2) to make a month index. We now have (hopefully) 3 numeric fields. Simple sanity checking of century converts 2-digit to 4-digit with reasonable surety for current dates (if its birthdate for example, it won't be earlier than today, so "08" is "1908" - and "order to be supplied by" isn't going to be in the past, but you can come unstuck with 2-digit dates easily). Month/day or day/month is easy for days >12. If its ambiguous, I usually state that and seek re-entry (or suggest one and confirm its right) Yeah. Painful. IMHO, converting database entries should involve converting from whatever form they WERE in, to unixtime - so forever more, they are in a standard form. Sadly, this applies to various things. Anyone remember a certain NASA mission that went horribly wrong because, um, *cough splutter* one team was working in metric and another in imperial, and nobody noticed? |
||||
| Gizmo Admin Group Joined: 05/06/2004 Location: AustraliaPosts: 5036 |
This is what I use. I have a general functions page included in every page ( Its an ASP based intranet, 900 plus pages ). Function usdate(tempd1) tempd1=replace(tempd1,"\","/") tempd1=replace(tempd1,"-","/") if isdate(tempd1) then usdate = Day(tempd1) & " " & MonthName(Month(tempd1)) & " " & Year(tempd1) & " " & Hour(tempd1) & ":" & Minute(tempd1) & ":" & Second(tempd1) else usdate="1 January 1900 00:00" end if end Function If I want to insert a date , I would use cn.execute "Insert into table ( datefield ) values ( '" & usdate(now) & "' )" Works ok most of the time. Yes the imperial v metric system is a big problem. I think is was a good idea for Australia to adopt the metric system when it did. I was in grade 3 I think when it came out, and we learned both systems. I remember at the time thinking the metric system was so easy compared to imperial. I was in the USA last year for a conference, and had a few problems talking technical. Lucky I had a grasp of the imperial system, "100mm is close to 4 inches", "water boils at 100, freezes at 0 degrees C" were two common conversions done in conversation. For interests sake, I have a HQ Monaro parked in the garage. 1973 model, big V8. Its speedo has BOTH km and miles scales. Glenn The best time to plant a tree was twenty years ago, the second best time is right now. JAQ |
||||
 Print this page |
