• P (unregistered)

    Pretty sure if they actually code the correct handling logic for the leap day it'd still be a WTF. So they're fucked either way.

  • hpoe (unregistered)

    Developers like this are the reason we can't have nice things.

  • STrRedWolf (unregistered)

    Ugh. At worst, build the date from the month and year, first of the month... then subtract one.

    ...and add a user function to actually do it in a sane manner.

  • Naomi (unregistered) in reply to STrRedWolf

    I'm curious, now, if there are any cases where this can fail.

  • (nodebb)

    Using CASE as a series of IF statements against the same expressions yielding (hopefully) the same answer each time is a WTF in this code.

    CASE Month(GetDate())-1
WHEN 2 THEN '28' -- Usually
WHEN 4 THEN '30'
WHEN 6 THEN '30'
WHEN 9 THEN '30'
WHEN 11 THEN '30'
ELSE '31'
END

    Of course, if EOMonth is unknown to you, (or your boss for some reason says to never use it), using other date functions get you where you need to go without flipping into strings.

    Set @EDate = DateAdd(second, -1, DateFromParts(Year(GetDate(), Month(GetDate(), 1))

    Go back one second from the beginning of the month. ;) No worrying about how long the previous month was, even during a leap year. Still, it is better to use the single function call rather than six of them.

    At least this is only being executed one and assigned to a variable. Imagine putting this construction in the actual SELECT statement.

    Addendum 2020-04-16 07:31: Oh, I did not notice STrRedWolf's proposal of the algorithm I wrote in code. Oh well, they are both in there.

  • Conradus (unregistered)

    "every database vendor has non-standard features"

    Which means you have decide how important portability is to you. If it's important to you, it's part of coding standards that you don't use non-standard features. It takes discipline, but it can be done.

  • Pudin9 (unregistered)

    EOMONTH was introduced in SQL Server 2012 (source: https://social.technet.microsoft.com/wiki/contents/articles/14331.microsoft-sql-server-2012-new-functions.aspx).

    Enterprise code (and especially frameworks) can easily have database code from before that, so not using a built-in is not necessarily a WTF by itself.

  • (nodebb)

    Years ago, there were some companies who treated Feb 28th as the end of month, and on the leap years the 29th was a "no business day"...... Survive long enough and one encounters many "wonders"....

  • WTFGuy (unregistered)

    I like the fact the original, and several of the proposed fixes, make multiple calls to GetDate(). Which ensures madness when called right around midnight and the return value changes from one invocation to another within the same execution of the expression.

    Rule 1 of homebrew date handling: Don't
    Rule 2 of homebrew date handling: See Rule 1.
    Rule 3 of homebrew data handling: If you're stubborn enough to still be reading, at least call your language's equivalent of GetDate() exactly once, assign the return value to a temp var, and do all your manipulation from that temp var.

  • Chris (unregistered) in reply to Nutster

    Going back one second may be problematic, if daylight savings started or ended then (not sure if this happens?). Or if a leap second gets added in. I'm not sure on any specifics of date-time stuff, just know enough to never do it myself ;)

    However, if forced, I think subtracting 12 hours ought to be OK?

  • WTFGuy (unregistered)

    And I forgot my double spaces on the end of my rules to have markdown treat CRs as CRs. Oops. Sorry.

  • MiserableOldGit (unregistered) in reply to TheCPUWizard

    I've worked in an organisation where the leap year/day is treated in different ways during the same calculation because where actuaries, bureaucracy and financial regulations collide, contradictory legal case rulings proliferate and chaos reigns supreme.
    They decided to outsource the lot to a hugely overpriced bunch of scamsters touting some fancy "artifical intellyjunz" and "machine learning" that would take all of their complex problems and just automagickally solve them without the need for us pesky, awkward "expensive" developers asking questions all the time and moaning about incomplete requirements.

    Needless to say, amongst the many, many problems was that the brillant genius script underneath had "solved" the complicated problem of leap years/days by treating every year as 365.25 days and everything else just became arithmetic. Oh how we laughed and laughed and laughed and laughed ... and then got told to fix it for them.

  • alexmagnus (unregistered) in reply to MiserableOldGit

    365.25 is even a wrong number. In the Gregorian calendar it is 365.2425 )

  • alexmagnus (unregistered)

    At least we don't use the traditional Chinese calendar, which has not one leap day but leap MONTHS, the month in question changing each time and the calculation for both which month should be repeated and in which year it should happen is quite complex.

  • (nodebb) in reply to Conradus

    Yes, it can be done. Usually at a significant expense in performance.

  • tbo (unregistered) in reply to WTFGuy

    At least in MySQL, calling NOW() to return the "current" time returns the same "current" time for the entire execution of a query, even if the time moves forward.

    https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_now

  • (nodebb) in reply to nerd4sale

    Yes, it can be done. Usually at a significant expense in performance.

    Often at significant expense to everything. If an application goes beyond CRUD, it's sometimes cheaper to code multiple database-specific data access layers than it is to code one data access layer that works on all of your target database engines.

  • Anon E. Mouse (unregistered)

    Basically, pick your evil empire (Microsoft, Oracle, Amazon, Google, et al) and live/die with the one you choose.

  • (nodebb) in reply to STrRedWolf

    and add a user function to actually do it in a sane manner.

    Though if you call it like SELECT ... FROM ... WHERE X = MYUSERFUNCTION(Y) then it can tank performance.

  • WTFGuy (unregistered) in reply to tbo

    @tbo: Interesting; I did not know that. Thanks.

    FWIW MSFT T-SQL definitely does NOT do that. You get whatever the underlying OS clock happened to read when it was queried by SQL Server's innards.

    But it looks like MySQL may still have that gotcha; albeit more subtly. From your ref:

    NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.)

    Imagine a stored function GetNow() that just returns NOW().
    What happens when we SELECT GetNow() = GetNow()?

    In my decidedly non-expert opinion, the docs seem to say you'll get FALSE every so often when the clock ticks between the left and right invocation. Likewise as I read it NOW() references within cascading triggers might not all get the same result either.

    Such fun.

  • blah (unregistered) in reply to WTFGuy

    @WTFGuy Not how I read that. Both invocations are part of the SELECT statement, so you get the "or triggering statement" branch.

    Postgres uses the start of transaction time. It has statement_timestamp() and clock_timestamp() for other behaviours.

  • Sole Purpose of Visit (unregistered)

    IANA Database Guy, so obviously I don't know what I'm talking about.

    But it seems to me that practically every WTF of this kind is the result of using SQL for the wrong purposes. It's been a standard since ... well, let's not go back to Codd in 1970, but at least it was published in 1986. And updated in 1989. And updated in ... and so on. But since IANA Database Guy, I have no idea how many breaking changes have been inserted into the standard since 1986. Very few, I would think. (Certain data types such as VARCHAR and possibly BLOB and maybe even EXTENTs come to mind.)

    Just use the damn thing for what it is. It's a relatively elegant implementation of set theory (with the inelegant addition of NULLs). It's almost organically receptive to the idea of DTDs and schemas in general. Not to mention migrations.

    Everything else is business logic. Business logic should not be mixed with a declarational language. Business logic does not belong in the domain of a database server. Why the hell should the database have to know about dates, except insofar as it might have a DATETIME type? (Which would be fine, if standardised for internationalisation.)

    You want reliable date conversions? Every single language out there has them in a library. Why bury them in the Potter's Field of a non-standardised stored procedure substrate?

  • (nodebb) in reply to WTFGuy

    What happens when we SELECT GetNow() = GetNow()?

    Every DBMS I've encountered tries to execute functions that don't depend on row data before executing the query. Most of them also turn reused expressions into a single execution. The result is that you get one GETDATE() or NOW() for the whole query.

    Also, things like ...

    SELECT LastName + ', ' + FirstName, COUNT(*) FROM People GROUP BY LastName + ', ' + FirstName

    ... only execute the expression "LastName + ', ' + FirstName" once per row.

    Both behaviors are performance optimizations from long ago. Non-deterministic functions don't have to return different data.

    I learned this back in the 1990s when I tried get get a random sample of a table with:

    SELECT * FROM mytable WHERE RAND() < .01

    This returns nothing 99.99% of the time and occasionally returns everything.

  • (nodebb) in reply to TheCPUWizard
    • Company that designated "the last day of the month" to be the 28th every month; anything that happened on a 29th, 30th, or 31st was rolled over to next month. I think they used to use the 20th so that their billing cycle would sync.
    • Company that abandoned months entirely and operated solely by-the-week on an ISO calendar. 13 weeks make a quarter except in ISO leap years when the last one is 14 weeks. Those are harder to account for than Gregorian leap years.
  • bst (unregistered)

    I was recently briefed a requirement to treat every month to have 30 days, resulting in 360 day years no matter what.

    I don't think being rooted in reality is their concern.

  • Old timer (unregistered)

    There are, or used to be, financial instruments priced with 31, 30, 28 day months. No 29. There are, or used to be, financial instruments priced on every conceivable method of day, month and year counting. 360 day years. Decimal dates. "Rent Day" calculations. And that's without even going into the agreed methods of fixed-point calculation.

    By the time I got to it, at least there was international agreement on floating point values. Floating point values are the values you'd get if you did the calculation in MS Excel.

  • At_most (unregistered)

    It could be a greater problem in other cases and studies. This was discussed and discovered, for the lack of a better word in Microsoft. There were simple wrong instructions in msinfo batch files. As to how to handle ENTRY and ENTITY collection. Or command. In most cases there was no access to server. And in clueless techniques. This is a personal idea. The time is calculated in seconds or milliseconds. Unless it's system of tables is logic proof you have to use an error errand that synchronized systems to internet time. Just another problem to synchronize to internet time.

  • Kj (unregistered) in reply to WTFGuy

    It's a non deterministic runtime constant. It's only evaluated once during execution. Maybe SQL would adjust how it estimates rowcounts doing something different from using the general density vector and using avg_range_rows instead for a HI_Key but that's just a guess.

    Rand() seems to be in a similar boat with the special treatment.

  • Aspie Architect (unregistered)

    T-SQL hasn't always had a EOMONTH() function. If I was doing it where there was no EOMONTH() function I would do a DATEADD of -1 day for the 1st day of the next month. either that or have some form of date table as per dimensional modelling. Truly trivial amounts of data for something that produces the capability to dice and slice date info by any means you see fit. SQL Server, Oracle, PostGres and even MySQL would find it hard to turn this into any significant workload for something as trivial as 100 years worth of data.

  • Griz (unregistered)

    Adopt a database. Its not just for Christmas, its for life.

  • (nodebb)

    You can easily get around the problem of database differences by using an ORM. That way your application can be locked to that for it's lifetime instead.

  • STATUS STATS (google)

    Yes, it can be done. Significant outflow in performance.

  • (nodebb) in reply to Zacrath

    Yes, you could indeed use the worst of both worlds.

  • Some Ed (unregistered)

    I actually worked for a company for a while which adjusted their processes so they did their end of month processing on the first of the month, processing data for all the days of the previous month whatever they might be. They had their own share of issues, but this one thing was simple.

Leave a comment on “A Leap to SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article