• bvs23bkv33 (unregistered)

    what do you mean fix it by 2020? it was tested in 1992!

  • (nodebb)

    Woah woah woah. Syntax highlighting? Nice.

  • NoLand (unregistered)

    A small step in a case-clause, but a big leap for some years…

  • Aspie (unregistered)

    If that is T-SQL then all that is to do a broken version of EOMONTH() which has been available since SQL Server 2012.

    For version prior to that it is SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    thanks to https://blog.sqlauthority.com for the latter.

  • RLB (unregistered)

    Whenever you find yourself writing "CAST(CAST(", reconsider.

  • Andrew Miller (unregistered)

    In the run up to year 2000 I swear I saw a letter in one of the computing weeklies basically saying "Everyone has got the leap year test for 2000 wrong, why don't we just redefine 2000 as NOT a leap year?". I fear he was serious....

  • (nodebb) in reply to RLB

    Even worse, CAST(CAST((CASE

  • snoofle (unregistered)

    But, but... Feb DOES have 28 days EVERY year!!!

  • Beliar (unregistered) in reply to snoofle

    Even better, EVERY month has 28 days every year.

  • (nodebb)

    Maybe, just maybe, SQL (of any flavour) isn't the right place to do this sort of calculation.

  • Gnasher729 (unregistered)

    Only halfwits got it wrong. People with no knowledge at all got it right, every 4th year is a leap year. Clever people got it right - there’s a 100 year and a 400 years exception. Knowing only the 100 year exception makes you a halfwit.

  • Gnasher729 (unregistered)

    By the way, there will be very rare problems if the code runs just before midnight.

  • Hasseman (unregistered) in reply to Gnasher729

    what time zone?

  • gumpy_gus (unregistered)

    Reminds me of when I was supporting a DOS application in 1997. Someone moved ahead the system date to 2000 and sent in a bug report, that the app got the year 2000 wrong. For those of you that are a little fuzzy on the concept, Every 4th year is a leap year, except every 100'th, which isn't, but that's still a bit off, so the rule is say yes to every 400'th. Our app, being an old DOS app, was unlikely to even make it to the year 2000, by 2000 most everybody would be using Windows, and the next bad day would be in the year 2400! Definitely post-DOS times. We declined to fix the bug.

  • Solitario (unregistered) in reply to Beliar

    Not true, October 1582 didn’t have 28 days, though it did have October 28.

  • snoofle (unregistered) in reply to Solitario

    A sunday, right?

  • Jeffrey Chadwell (google) in reply to Solitario

    That's not universally true - it depends on when the switch was made from the Julian calendar to the Gregorian calendar. For instance, the UK and its colonies (including what would become the US) didn't make the switch until 1752, and the short month that year was September.

  • (nodebb) in reply to RLB
    Whenever you find yourself writing "CAST(CAST(", reconsider.
    Isn't that double-safe casting, just like ROT13(ROT13(text)) is double encrypted?
  • (nodebb)

    Well somebody seems to have been using that little poem: 30 days has September, April, June and November All the rest have 31 And February’s great with 28 And Leap Year’s February’s fine with 29

    I guess they forgot that last line.

  • Little Bobby Tables (unregistered)

    Q. what's the longest month of the year?

    A. October. It's got an extra hour.

  • Bruce (unregistered) in reply to Gnasher729

    Actually it won't have problems - All GetDates within a SQL statement will return the same value.

  • Bruce (unregistered) in reply to Gnasher729

    Actually it won't have problems - All GetDates within a SQL statement will return the same value.

  • sizer99 (google)

    I 'like' how he's doing GETMONTH() -1 all over the place. Since he's just comparing them to month constants it'd be faster, easier, and clearer just to compare against normal 1-based month numbers. 11 should be November, not December.

  • snoofle (unregistered) in reply to sizer99

    ..what about those 13-month calendars?

  • Pedantry in Motion (unregistered) in reply to Beliar

    Except September 1752 in England...

  • Vicki (unregistered)

    Nobody's raised this in all the kerfuffle about the leap years - although maybe that's due to it being so obvious as to be unworthy of remark, or because I'm misinterpreting things. Shouldn't it be MONTH(GETDATE())+1, not MONTH(GETDATE())-1? You want to translate FROM a zero-indexed value returned by MONTH() TO a one-indexed month number for proper comparison. That's adding one, not subtracting. You'll sometimes get the right answer anyway because the long and short months tend to alternate, but I don't think you have until 2020 to fix this.

  • (nodebb)

    We should just switch to a 28 day, 13 month calendar, and just change the 13th month to be either 29 or 30 days for leap years.

  • Anonymous') OR 1=1; DROP TABLE wtf; -- (unregistered) in reply to Little Bobby Tables

    In North America, the extra hour from the end of Daylight Saving Time is in November, not October, so the six of the seven 31-day months are all tied for the longest (with March being one hour shorter from the start of DST).

  • Darkenon (unregistered) in reply to Aspie

    You can also use LAST_DAY in MySQL, postgres and many other databases to find the end of the month.

  • Derf Skren (unregistered)

    With all those repeated calls to GETDATE() I just can't wait to see the confusion when it rolls over the month end midway through evaluating the function.

  • Aaron Togonon (unregistered)

    can you help an idiot like my facebook account was been hacked ang the link was changed and i copy it and paste it on google and i saw this site how can i retrieve my account??

  • (nodebb) in reply to sizer99

    I mean, whoever wrote it did know about variables, but the thought of using one to store MONTH(GETDATE())-1 just didn't happen.

  • (nodebb) in reply to sizer99

    I mean, whoever wrote it did know about variables, but the thought of using one to store MONTH(GETDATE())-1 just didn't happen.

    Addendum 2018-12-19 09:12: Do you think this forum software is sophisticated enough to detect double-submissions?

  • Thunder (unregistered)

    Last Christmas I gave you my code, but the very next leap year you gave it away. This year, to save me from tears I'll give it to DailyWTF.

  • I'm not a robot (unregistered) in reply to Watson

    I mean, whoever wrote these two comments did know about variables, but the thought of using one to store "I mean, whoever wrote it did know about variables, but the thought of using one to store MONTH(GETDATE())-1 just didn't happen." just didn't happen.

  • (nodebb) in reply to Aspie

    Although for the last day of the previous month this Stack Overflow answer has a nice solution:

    DECLARE @date datetime = GETDATE()
SELECT DATEADD(DAY, -(DAY(@date)), @date)

    https://stackoverflow.com/a/29474852

  • Si (unregistered) in reply to Aspie

    Although subtracting one day might be a bit of a safer bet, like:

    SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

    Reason being if you were to insert the value that subtracts one second into a SMALLDATETIME column (or set a variable of that type) it'd round it back up to the first day of the current month :s

  • (nodebb) in reply to Derf Skren

    I used to flag that sort of stuff during code reviews. It always frustrated me that the same people that would do something like

    bool b;
if (someparameter != 0) {b = true;}
if (b) {somefunction();}

    would, over and over, refuse to understand why calling a moving target like GETDATE() repeatedly was a bad idea.

  • Si (unregistered) in reply to urkerab

    Damn, that is pretty nice, though still liable to return the incorrect date for SMALLDATETIME during the last 30 seconds of the day (and other data types/resolutions during the last n fractional seconds of the day):

    DECLARE @dt DATETIME = '20181218 23:59:30', @sdt SMALLDATETIME; SET @sdt = DATEADD(DAY, -DAY(@dt), @dt); SELECT @sdt;

    Different datetime resolutions are great until they throw up a difficult-to-replicate bug. I guess that's more of a general problem with altering the precision of things on the fly though.

  • Protestant (unregistered) in reply to Solitario

    Says who? The pope?

  • jay (unregistered)

    "the UK and its colonies (including what would become the US) didn't make the switch until 1752, and the short month that year was September."

    I wonder if landlords still charged a full month's rent.

  • jay (unregistered)

    "We should just switch to a 28 day, 13 month calendar, and just change the 13th month to be either 29 or 30 days for leap years."

    Or say that a year is 364 days. Who cares if the calendar year matches the solar year? So seasons would start one day earlier each year. So what? The convenience of having a more systematic calendar would be well worth it.

  • (nodebb) in reply to jay

    Hmmm probably not, there are cultural traditions associated with certain dates and seasons, a shifting year wouldn't work.

  • (nodebb)

    Alternatively to maintain quarters, we can do 28 28 35 28 28 35 28 28 35 28 28 36/37. We keep 7 day weeks, 12 months, quarters. Ideally we reset the year, January 1 is always Sunday.

    For time, we split earth day into 100000 seconds (comparable to current seconds). 20 hours, 50 minutes, 100 seconds.

  • (nodebb)

    But 28 is approximately correct for February 2020?!

  • G (unregistered) in reply to Vicki

    No. Month in T-SQL returns a value 1 to 12. He's subtracting 1 to get the previous month.

Leave a comment on “A Short Leap”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article