• (nodebb)

    If you check the docs on the function, you'll see that if you don't supply a format, it defaults to whatever is set in your internationalization settings, and Oracle recommends that you don't rely on that.

    I assume that's why they defined their own default c_date_format.

  • (nodebb) in reply to nerd4sale

    I assume that's why they defined their own default c_date_format.

    With the "c_" prefix presumably being a sub-Hungarian notation for "clobal"...

  • Hasseman (unregistered)

    Seen applications storing dates as strings. Not terrible wrong if you ensure they are stored in ISO (yyyyMMdd) and possibly TZ info added

  • Yazeran (unregistered) in reply to Hasseman

    Well if yoy store dates as strings what are preventing you to store '20250230' as a date :)

    Any competent db soulld catch that one as an knvalid date but it is a perfectly valid string.

    Oh, and how do you do date calculations on the strings - yes you do the horror of inventing your own.

  • (nodebb) in reply to Steve_The_Cynic

    With the "c_" prefix presumably being a sub-Hungarian notation for "clobal"...

    You must be new here. It clearly stands for "clbuttic".

  • Greg (unregistered)

    Maybe the c in the date format suggests they're using %c to have a numeric representation of the month?

  • David-T (unregistered) in reply to Yazeran

    YYYYMMDD does at least collate properly, so you can do calculations (for past/future dates) on dates, convert them to strings, use them as range endpoints and comparisons will work properly.

    I know this because SAP stores dates as strings... sigh...

  • Hal (unregistered) in reply to nerd4sale

    I wonder how smart salting the To_Date with the format. After all if you name your salted function CONVERT_STRING_TO_DATE it is not like you are saving a whole lot of keystrokes or making it so that query expressions are going to format more readable.

    Someone less experienced with Oracle might not realize the implications and be left wondering, WTF why don't these queries respect the locale settings. I am not seeing the meaningful reduction in complexity, convenience, or conciseness here to justify the increased obscurity. Especially when you can just not specify the format when the server is correctly configured. Just put a comment at the top of your procedure, file, whatever that explains the locality is assumed.

  • RLB (unregistered)

    I mean, using PHP/MySql you get a date as a string formatted like 'YYYY-MM-DD' from the database, so sending it back in the same manner doesn't sound crazy to me. As, indeed, you also do.

  • (nodebb)

    Ref

    With the "c_" prefix presumably being a sub-Hungarian notation for "clobal"...

    I bet the leading c is for "const".

  • (nodebb) in reply to WTFGuy

    Yeah, good chance it is.

  • (nodebb) in reply to Hasseman

    Seen applications storing dates as strings. Not terrible wrong if you ensure they are stored in ISO (yyyyMMdd) and possibly TZ info added

    Sure. As long as all you are doing is storing dates. At the very least, you've now pushed the task of re-hydrating your strings as platform-specific dates to every single consumer of this data. Also, you've made all of the built-in date functions either useless, or harder to use.

    You've made ORMs less likely to know what the underlying type is and therefore to not enforce any date-ness until the data is actually saved and hits your database constraints.

    You've made sorting potentially more difficult, and possibly performance killing. Imagine trying to sort data that has time zone information in the date strings and not all of the time zones are the same. You'd have to do a normalization step first, either to convert all to your crazy date-string in the same time zone, or to convert to actual dates.

    All this to justify your "not terribly wrong" idea of not using the correct data type. You know what's both easier and also not terribly wrong - just using dates.

    I hate programmers and the far-too-pervasive attitude of "works for me".

  • (nodebb)

    Not so hiddin WTF: Saying things like "The badness is not that they're using Oracle, though that's always bad" when Oracle (or any other database, for that matter) had nothing to do with this.

    Storing dates as strings in YYYYMMDD format is perfectly fine if you are learning to write and read data to/from flat files and do sorting while taking a "<Insert Language Here> for Dummies" course designed as "Programming 101". But once you learn that <Insert Language Here> has typely data, you should use types. Always. Stuff like this makes me wonder what kind of WTFery we'd have in the wild if BASIC had data types besides Number and String.

  • DigitalBits (unregistered) in reply to Yazeran

    While you should use the date provided by the DB, this isn't always sufficient. For example, MS SQL Server doesn't support dates with timezones, only Date, Time, DateTime, or DateTimeOffset. The only way to store it is to use 1 string field, or 2 fields (string & DateTime).

  • Seirios (unregistered) in reply to Hal

    Looking at the code, and seeing the use of a constant as the default date format, the function must be present in a package and type even more than CONVERT_STRING_TO_DATE.

    Locale settings in Oracle is a major WTF. Bear in mind, this is set on the client side (and this date is stored server side), and if you don't specify your locale explicitly in your client, you will descend into the seven circles of hell to find out what exact settings will be used, ranging from OS region, JVM locals to init.sql statements.

  • (nodebb) in reply to Steve_The_Cynic

    I think the c_ prefix is for clevr_ ... because solving the difficult problem of ensuring dates in the database are human-readable is almost, but not quite, brillant.

  • Yazeran (unregistered) in reply to DigitalBits

    Ok thats new.

    I have only been working with postgres and that have had timestamp with timezone info for as long as i have worked with it (15+ years) so I assumed that all major DB engines had that as well....

    I only use timestamp without timezone as we are only in one timezone and the DB I work with is NOT avaliable outside my organisation, so there have been no reason to include timezone information in the user interface.

  • Tim (unregistered)

    If you're storing dates without a time component, and your tech stack doesn't have a date-without-time type at every level, you're in for a world of hurt trying to use date/time types because of time zones - e.g retrieving a serialised date from JSON and displaying it to the user, your code will be full of off-by-one errors. It's usually far safer to use strings of yyyymmdd throughout except on the edges of the system.

  • (nodebb) in reply to Hal

    I agree. The default date format can be set on the database, and even on session level, negating the need for this custom date conversion function.

  • (nodebb) in reply to DigitalBits

    For example, MS SQL Server doesn't support dates with timezones, only Date, Time, DateTime, or DateTimeOffset.

    DateTimeOffset supports time zones.

Leave a comment on “Wrap Up Your Date”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article