Exploring the DATE Functions in SQL

  • beervolk

    Grasshopper

    Points: 13

    From my experience it is safe to use something like:

    dateColumn >= '20200101' AND dateColumn < '20210101'

    for any DATE/DATETIME of 2020 dates instead of dateColumn BETWEEN ...

    plus those strings of dates without dashes 'yyyymmdd' (style 112) is preferred to me 🙂

    and in dim.Time (DWH for later OLAP) I prefer to have time_key as INT in form of YEAR*10000+MONTH*100+DAY.

    just sharing ... do not bit me much 😉

    • This reply was modified 4 months, 1 week ago by  beervolk.
  • nitin.s.pathak 96551

    SSC Journeyman

    Points: 76

    Accidentally there is mistake while explaining syntax of datediff

    Please refer below in bold letters

    The syntax for the DATEDIFF function is as follows:

    DATEADD(DATEPART, STARTDATE, ENDDATE)

    The arguments accepted by the DATEADD function are as follows:

  • Jeff Moden

    SSC Guru

    Points: 996622

    Just my humble opinion but, except for the fact that the newer datatypes can use fewer bytes, they pretty much suck for me because you cannot do direct data math with them, which is non-ANSI/ISO compliant.  The standards do say that EndDate-StartDate = Period and StartDate+Period = EndDate, etc.

    Microsoft apparently realized the boo-boo they made because of all the people wanking about not being able to easily calculate periods to the millisecond but instead of fixing the real problem, they introduced DATEDIFF_BIG.

    As for the EOMONTH function, I'd have much rather had an FOMONTH function that would return a DATETIME datatype.

    So far as dates prior to 01 Jan 1753, I wouldn't use SQL Server for them if I needed to work with them because of all the calendar changes made prior to that date.  In other words, the dates are actually incorrect prior to that date depending on which country and religion you're working with.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ckredo

    Grasshopper

    Points: 16

    Just a quick comment re. DateAdd.  I just got myself into trouble with a DateAdd function that was adding 1 year to 2/28/2019.  The application was working with budgets that had a variety of end dates, with each end date always the last day in the month.  We've used this application for a couple years now with no issue.  Then along comes the leap year in 2020!  Dateadd(year,1,'2/28/19') will return 2/28/2020.  That makes sense - but it's not the end of the month!  The fix is easy with an EOMONTH check, and maybe this audience won't be silly enough to forget about leap years but.....!!

  • Jeff Moden

    SSC Guru

    Points: 996622

    ckredo wrote:

    Just a quick comment re. DateAdd.  I just got myself into trouble with a DateAdd function that was adding 1 year to 2/28/2019.  The application was working with budgets that had a variety of end dates, with each end date always the last day in the month.  We've used this application for a couple years now with no issue.  Then along comes the leap year in 2020!  Dateadd(year,1,'2/28/19') will return 2/28/2020.  That makes sense - but it's not the end of the month!  The fix is easy with an EOMONTH check, and maybe this audience won't be silly enough to forget about leap years but.....!!

     

    EOMONTH does make the fix easy... provided that your column data doesn't also store times other than midnight.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply