When you store dates in a text field



  • FormatCount
    x/x/xxxx5
    xx/x/xxxx24
    x/x/xx4
    xx-xx-xx1
    xx.xx.xxxx176
    xx/xx/xxxx4665
    xx.xx.xx206
    xx/x/xx12
    x/xx/xxxx572
    xx/xx/xx917
    x/xx/xx7
    xx-xx-xxxx11
    xx.x.xx9
    Of course, now I have to pull this out and convert it into an actual date.

    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.


  • Winner of the 2016 Presidential Election

    I'm sorry to hear that... How many are backwards?


  • Winner of the 2016 Presidential Election

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


  • Winner of the 2016 Presidential Election

    @Scarlet_Manuka said:

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



  • Month_PartCount
    1435
    2522
    3497
    4507
    5588
    6591
    7671
    8732
    9642
    10543
    11484
    12397
    I'm happy with that.


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


  • area_deu

    @Scarlet_Manuka said:

    (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?


  • area_deu

    Related DC WTF:

    There's only one allowed format for auto-close timestamps:

    YYYY-MM-DD HH:mm
    

    No T allowed, no seconds allowed.


  • area_deu


  • area_deu


  • area_deu

    Belgium.


  • Winner of the 2016 Presidential Election

    @fbmac said:

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

    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.


  • area_deu

    @fbmac said:

    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.


  • Discourse touched me in a no-no place

    @Onyx said:

    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.


  • Winner of the 2016 Presidential Election

    Ok, bad example, I admit.

    How about a telephone number written as 0xx / xxx-xxxx (private mobile, don't call)



  • @Scarlet_Manuka said:

    xx/xx/xxxx 4665
    American or European?


  • Discourse touched me in a no-no place

    @DogsB said:

    American or European?

    Unladen.



  • Racist!



  • @fbmac said:

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



  • @fbmac said:

    I heard sap r3 stored everything as text, is that true? Why would they do that?

    Germans.


  • area_deu

    @blakeyrat said:

    Germans.

    HEY!


  • area_deu

    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!


  • area_deu

    @AlexMedia said:

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

    QFT. They aren't even discoverable for Germans.



  • @Scarlet_Manuka said:

    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.



  • @ChrisH said:

    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.


  • area_deu

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



  • @ChrisH said:

    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?


  • Impossible Mission Players - A

    You forgot XXXXXXXX!!!



  • @David_C said:

    What I love about that particular RFC is that, unlike other April 1 RFCs, this one is implementable.

    1149 has been done.


  • Impossible Mission Players - A

    @Steve_The_Cynic said:

    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!



  • @Steve_The_Cynic said:

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


  • Discourse touched me in a no-no place

    @Steve_The_Cynic said:

    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.



  • @ChrisH said:

    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.

    @DogsB said:

    American or European?

    No.

    @Steve_The_Cynic said:

    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)



  • @ChrisH said:

    Also you obviously don't know that SAP stands for Software Anus Pollen...

    FTFHMBORI


  • area_deu

    @Scarlet_Manuka said:

    Yes, but users are entering this data, so why would you expect it to arrive in a sensible format?

    Training?


  • sockdevs

    @CoyneTheDup said:

    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!


  • @Scarlet_Manuka said:

    this is part of a set of user-defined attributes

    See Also: Inner Platform Effect.


  • Discourse touched me in a no-no place

    @powerlord said:

    See Also: Inner Platform Effect.

    Not enough inner platform!

    field_name	field_value
    see also	Inner Platform Effect
    


  • @accalia said:

    ...there is only one true data format....

    That gave me a good :rofl:.

    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 CST

    Ovs, the code starts with:
    ***** WARNING *****
    The following code is a huge black-box system and MUST NOT be meddled with !!!!
    ***** WARNING *****


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.