When you store dates in a text field
-
Format Count x/x/xxxx 5 xx/x/xxxx 24 x/x/xx 4 xx-xx-xx 1 xx.xx.xxxx 176 xx/xx/xxxx 4665 xx.xx.xx 206 xx/x/xx 12 x/xx/xxxx 572 xx/xx/xx 917 x/xx/xx 7 xx-xx-xxxx 11 xx.x.xx 9 I'm not criticising the design decision, incidentally, because this is part of a set of user-defined attributes, and obviously most of those are text so storing the value in a text field is not that much of a :WTF:. (I've also seen systems that have separate storage for the attribute value based on the datatype, but even then dates don't often get a look-in.) It's just more a commentary on how you can't get users to stick to any one way of doing things.
-
I'm sorry to hear that... How many are backwards?
-
I'm... very sorry.
-
Haven't checked yet, but I'm not expecting that to be too much of a problem. D/M/Y is pretty much ingrained here as a standard order (and you'll note that there's no four-digit parts anywhere except at the end, so I don't have to parse YYYY-MM-DD as well).
-
Haven't checked yet, but I'm not expecting that to be too much of a problem. D/M/Y is pretty much ingrained here as a standard order (and you'll note that there's no four-digit parts anywhere except at the end, so I don't have to parse YYYY-MM-DD as well).
You say that, buuuuuuttttt...
-
I'm happy with that.Month_Part Count 1 435 2 522 3 497 4 507 5 588 6 591 7 671 8 732 9 642 10 543 11 484 12 397
-
How many of them have day_part ≤ 12?
-
I should expect about 40% of them.
TR:WTF: is that it turns out that this information already exists in processed form and I hadn't noticed it when I was originally mapping out where all this data was coming from.
TRR:WTF: is that, contrary to any other time I have attempted to use date format models in Oracle, instead of complaining that the format model doesn't match up it happily converts ALL of these to the correct date with
TO_DATE(value, 'DD/MM/YY')
. I found this out by checking where that processed value was coming from and couldn't believe it at first - I had to do an extra check to verify that yes, it was picking up the alternate separators and non-zero-padded bits and actually getting the right result.Partly this is explained by the documentation - it says that when
TO_DATE()
can't match the format string, it tries with alternate formats for each part, so if YY doesn't match the year part then it'll try to parse it with YYYY. It looks like it's also doing it with MM -> M and DD -> D, although those aren't listed in the table of alternate formats that it tries. (Actually, it's probably that MM and DD represent optionally zero-padded numbers, I guess.)I also eventually found this:
@Oracle said:Any non-alphanumeric character is allowed to match the punctuation characters in the format model.
So... thanks, Oracle, for Doing It Right™ on that one.
-
(and you'll note that there's no four-digit parts anywhere except at the end, so I don't have to parse YYYY-MM-DD as well).
Pity. ISO-8601 is the only sensible date format in existence.
-
I heard sap r3 stored everything as text, is that true? Why would they do that?
-
Related DC WTF:
There's only one allowed format for auto-close timestamps:
YYYY-MM-DD HH:mm
No T allowed, no seconds allowed.
-
-
-
Belgium.
-
Why would they do that?
My guess? Someone got sick of sanitizing user input. I saw a CRM-y type thing once where almost everything was plaintext. In field that any sane person would use to write something like
123 Fake St.
there was shit like123 Fake St., entry through an archway, second door on the left
. Do note there was a perfectly good "Notes" field like 50 pixels away!Now imagine trying to parse that shit. I'm not saying that excuses them, but I kinda see why someone might just say "fuck it" and let the users write whatever the shit they want instead of dealing with their complaints when such idiocy doesn't work.
-
I heard sap r3 stored everything as text, is that true?
Yes.Why would they do that?
Because fuck you, that's why.I can't count the times where SAP fucked up e.g. some serial number search because our apps store the serial NUMBER numerically and SAP stores it as text, with leading zeroes or blanks or "dont know yet" or "ask John" added as and when the entering user or system felt like it.
And when you have the SAP interface sorted out, along comes another 3rd party app which has the leading zeros normalized to yet another length and all non-numeric characters converted to "?".Interfaces suck. SAP interfaces suck even more.
-
I saw a CRM-y type thing once where almost everything was plaintext. In field that any sane person would use to write something like 123 Fake St. there was shit like 123 Fake St., entry through an archway, second door on the left. Do note there was a perfectly good "Notes" field like 50 pixels away!
You wait until you get an address like
Trumpton Manor, Trumpton
. There's no number there because there's no number: the name is unique. Address validation is insanely difficult, so you usually shouldn't bother at all.
-
Ok, bad example, I admit.
How about a telephone number written as
0xx / xxx-xxxx (private mobile, don't call)
-
xx/xx/xxxx 4665
American or European?
-
-
Racist!
-
I heard sap r3 stored everything as text, is that true? Why would they do that?
It's SAP, of course it sucks and doesn't make any sense.
Another fine example of why SAP sucks: some of their field names are in German, and then they use some retarded abbreviation. GLTGV = Gültig Von = Valid From.
-
I heard sap r3 stored everything as text, is that true? Why would they do that?
Germans.
-
-
Also you obviously don't know that SAP stands for Software Aus Polen...
-
The code might be written in Poland, but it's Germans who make our spec!
-
Another fine example of why SAP sucks: some of their field names are in German, and then they use some retarded abbreviation. GLTGV = Gültig Von = Valid From.
QFT. They aren't even discoverable for Germans.
-
I found this out by checking where that processed value was coming from and couldn't believe it at first - I had to do an extra check to verify that yes, it was picking up the alternate separators and non-zero-padded bits and actually getting the right result.
Really? Are you sure? If someone entered 10/11/12 as his date of birth, because he'd been doing it all his life and why should he change now that he's 103?, what does it do? Is he still 103 or has he become just 3.(Consumer or 'retail' banks had to deal with Y2K starting back in 1970 in the US, and 1975 in the UK, to avoid calling customers out for having overdue mortgage payments before the first one was even due. I'm not saying they necessarily dealt with it in the best way, but they dealt with it.)
(But then again, some of the problems highlighted by the Y2K floo-flah existed long before electronic digital computers were a thing. For ... reasons ... I spent a day reading 19th Century documents in the British Library Reading Room in London. I stumbled across a printed form which included a field (blank space with underline) for the year to which the form applied. The form was a product of the 1850s, and the following year, they would need a new form, because the date field looked like this:
[code]185__[/code]
The hand-written digit in the field was a 9.
-
ISO-8601 is the only sensible date format in existence.
But it will blow up in the year 10,000. [URL=http://tools.ietf.org/html/rfc2550]RFC 2550 - Y10K and Beyond[/URL] is a better solution because it will never need to be changed all the way into the infinite future.
-
Unlike mm/dd/yy which will blow up every 100 years. We're just lucky there were no computers last time.
(And yes, I saw the date on that RFC)
-
Y10K
-
And yes, I saw the date on that RFC
What I love about that particular RFC is that, unlike other April 1 RFCs, this one is implementable. The practical use is questionable, but there is no technical reason why it couldn't be supported. (For all I know, there are already libraries to support this for all the popular programming languages, but I haven't bothered looking.)
-
Is SAP worse than Oracle?
-
You forgot XXXXXXXX!!!
-
What I love about that particular RFC is that, unlike other April 1 RFCs, this one is implementable.
1149 has been done.
-
The hand-written digit in the field was a 9.
Because obviously they would need to save the filler from needing to write an extra number! That's too hard!
-
1149 has been done.
I didn't realize it was tried. Round trip time of 1.5 hours isn't quite going to be good enough for playing WoW though. :-)
-
1149 has been done.
So has 3252. We used it one time to tunnel networking past a particularly obstreperous firewall and web proxy. (It only did GET, and only in fully cached mode. All other ports were entirely closed. Awful site to hold a meeting.)
-
One common tenet among April fools' RFCs is to take the unlimited theoretical combineability of network protocols to their most perverse extremes.
-
Pity. ISO-8601 is the only sensible date format in existence.
Yes, but users are entering this data, so why would you expect it to arrive in a sensible format?I'm just happy it's in a consistent order. Doesn't matter much which order it's in as long as it's the same.
American or European?
No.Really? Are you sure? If someone entered 10/11/12 as his date of birth
Not a problem for this data set, all the dates are post 2000. And in general - supposing the unlikely scenario that this solution makes it to 2100 - all the dates will be within the last 30 years or so at any time, so the default century correction will work properly for this.
-
Wait until they insist you have to be able to tell the difference between...
11/01/12 (mm/dd/yy)
and
01/11/12 (dd/mm/yy)
and
12/01/11 (yy/mm/dd)
-
-
Yes, but users are entering this data, so why would you expect it to arrive in a sensible format?
Training?
-
Wait until they insist you have to be able to tell the difference between...
that's easy, there is only one true data format so......11/01/12 (January 12, 0011)
01/11/12 (November 12, 0001)
12/01/11 (January 11, 0012)
-
This post is deleted!
-
this is part of a set of user-defined attributes
See Also: Inner Platform Effect.
-
See Also: Inner Platform Effect.
Not enough inner platform!field_name field_value see also Inner Platform Effect
-
...the
re isonly one true data format....That gave me a good .
I work on IBM z/OS...we can't even get one true date format on the one system.
"the one true date format" should be listed as a meme...or maybe "the date format to rule them all".
-
No, according to RFC 2550, you have those wrong. The year is always at least four digits long and the date can be omitted.
-
To continue the Python theme.... you lucky lucky b*****d. I've had to deal with:
Date : Saturday, 30 August 1980 15:42-MDT
Date : Monday, 1 September 1980 15:42-MDT
Date : Wed Oct 3 20:10:02 2002
Date : Wed Oct 30 20:10:02 2002
Date : 2 Oct 96 17:39:40
Date : 23 Oct 96 17:39:40
Date : 1 Dec 1997 10:34 EST
Date : 16 Dec 1997 10:34 EST
Date : Thu, 1 May 92 11:13:20 BST
Date : Thu, 21 May 92 11:13:20 BST
Date : Sat, 05 June 1980 5:42-MDT
Date : Sat, 3 Aug 1980 15:42:00 +0000
Date : Sat, 30 Aug 1980 15:42:00 +0000
Date : Fri Nov 26 1982 14:53:23 PST
Date : Fri Nov 2 1982 14:53:23 PST
Date : Fri 12 Jun 84 06:38:52
Date : Fri 2 Jun 84 06:38:52
Date : 22 August 1982 06:40-EDT
Date : 2 August 1982 06:40-EDT
Date : January 11, 1985
Date : Tuesday, August 18, 1987
Date : 31 March 1986, 14:30:32 CSTOvs, the code starts with:
***** WARNING *****
The following code is a huge black-box system and MUST NOT be meddled with !!!!
***** WARNING *****