{"id":1769,"date":"2014-03-06T00:00:00","date_gmt":"2014-03-06T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/on-handling-dates-in-sql\/"},"modified":"2021-09-29T16:21:39","modified_gmt":"2021-09-29T16:21:39","slug":"on-handling-dates-in-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/on-handling-dates-in-sql\/","title":{"rendered":"On Handling Dates in SQL"},"content":{"rendered":"
“I’ve been on a calendar, but I’ve never been on time.”
–Marilyn Monroe <\/p>\n

The most international of all units of measurement on earth is the Common Era Calendar. This used to be known as the “Gregorian Calendar”, “Western Calendar” or “Christian Calendar “when it was issued by Pope Gregory in 1582. And the original company is still in business. There have been no upgrades since then, unless you want to count adding local names for the months. For example, Czech months <\/a>  are of Slavic origin (Åíjen<\/i>), whereas the Slovak months are of Latin origin (Október<\/i>), but most languages use the Latin roots. But I am in database, so I only care about the subset of ISO-8601 display formats used in my language. The only use of the term ‘ field ‘ in SQL is for the parts of a timestamp (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND<\/code> ); for this article, all I care about is the DATE<\/code> set of fields (YEAR, MONTH, DAY <\/code> ). Do not panic, this subset is good enough for most commercial work. Those of you who deal with museums, talk to me off line. <\/p>\n

Today, every SQL implementation has a DATE<\/code> data type, but the functions available for them vary quite a bit. The most common ones are a constructor that builds a date from integers or strings; extractors to pull out the month, day, or year; and some display options to format output. Do not mimic 1970’s COBOL and format data for display, using a proprietary vendor feature. <\/p>\n

In Standard SQL, the constructor is “CAST (<string expression> AS DATE)<\/code>” for a string expression. Hey, we have this in T-SQL now! In Standard SQL, the only<\/i> ISO-8601 format “yyyy-mm-dd<\/code>” allowed for date values. Hey, we have this is how T-SQL displays by default in the new DATE<\/code>, TIME<\/code> and DATE TIME2(n)<\/code>! Do you think that Microsoft is trying to catch up? The ANSI Standard also has “DATE (<string expression>)<\/code>” option lacking in T-SQL, but that is simple syntactic sugar. <\/p>\n

The ISO-8601 Standard has other date and time formats which are not ambiguous and could be added to the strings recognized by SQL. But we had to make a decision for the SQL Standard, so that we have one and only one way to work with dates. This avoids problems like confusing the British (“dd\/mm\/yy<\/code>“), American (“mm\/dd\/yy<\/code>“) and other national traditional shorthands. This format is also embedded in lots of other ISO Standards, hardware counters and anything that has to be sorted in temporal order. We picked the most readable, most universal one we had. <\/p>\n

SQL Server still has the 1970’s Sybase\/UNIX convert() string function. This is because when it was written, we did not know how to use tiered architectures. Client\/Server was brand new! We had decades of monolithic COBOL as the only<\/i> programming model (yes, I am that old). This means that all the display formatting is mixed in the the other code for data retrieval, computations, etc. To accommodate this, Sybase added convert() with local, non-standard displays for dates. There was no concept of independent presentation layers, separated from the database layer, doing local translations, computations and all the display work. <\/p>\n

Sybase also added MONEY<\/code> as a data type to put punctuation marks (currency symbol, comma, decimal point) in currency display. MONEY<\/code> is another issue; it does not do correct math, does not port and has illegal precision for many GAAP and EU regulations. This was done with a PICTURE<\/code> spec in COBOL (http:\/\/en.wikipedia.org\/wiki\/Picture_clause), if you want to see where Sybase got this. <\/p>\n

The ANSI\/ISO extractor for is the function EXTRACT(<extract field> FROM <extract source>) <\/code> <\/p>\n

The extract field options for dates are a little easier to see this in a table <\/p>\n\n\n\n\n\n\n
\n

\t\t\tMeaning of <primary \t\t\tdate field> <\/b><\/p>\n<\/td>\n

\n

\t\t\tKeyword <\/b><\/p>\n<\/td>\n<\/tr>\n

\n

\t\t\tYear in Common Era \t\t\tCalendar (returns 0001-9999) <\/p>\n<\/td>\n

\n

\t\t\tYEAR<\/code> <\/p>\n<\/td>\n<\/tr>\n

\n

\t\t\tMonth \t\t\twithin year (returns 01-12) <\/p>\n<\/td>\n

\n

\t\t\tMONTH<\/code> <\/p>\n<\/td>\n<\/tr>\n

\n

\t\t\tDay within \t\t\tmonth (returns 01-31) <\/p>\n<\/td>\n

\n

\t\t\tDAY<\/code> <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The obvious ordering is from most significant to least significant: YEAR<\/code>,MONTH<\/code> <\/code> and DAY<\/code> . <\/p>\n

The date fields contain non-negative integer values, constrained by the natural rules for dates using the Common Era calendar. Please note a few things you might not think about if you are not a “data junkie” who stayed up to watch the leap-second one night. <\/p>\n

There is no year zero. There are no negative years, either. The correct postfix is “CE”, and “BCE” for “Common Era” and “Before Common Era” dates, replacing AD (“Anno Domini ‘) and BC (“Before Christ). <\/p>\n

Standard SQL also has an INTERVAL <\/code> data type. This is a “mixed measurement”, made up of temporal fields, just as we Americans mix feet and inches. An INTERVAL <\/code> is a period of time, such as “3 years 2 months” or “90 days”; in T-SQL we have to use a single unit of temporal measurement in a parameter in an old Sybase function call to DATEADD()<\/code> , DATEDIFF()<\/code> and so forth. <\/p>\n

The reason for the Sybase model is historical. The temporal data types were not in the Standards and the Sybase clock was based on a floating point number in the hardware. Following the UNIX model, we used clock ticks on the old 16-bit hardware. It works well with C programs, too. <\/p>\n

There are effectively two kinds of INTERVAL<\/code> sub-types for using with dates (there are more for time fields , but I am not talking about it here). <\/p>\n

    \n
  1. YEAR<\/code> –MONTH<\/code> contains one or both YEAR <\/code> and MONTH <\/code> fields. <\/li>\n
  2. “DAY<\/code> ” contains a positive or negative number of days. This looks like what we do in T-SQL, but without the \t\tmixed units and different syntax. . <\/li>\n<\/ol>\n

    \tThe reason for the two interval types is to avoid the ambiguity that would arise if a MONTH \t <\/code> value was combined with a field of lower significance, e.g. DAY<\/code>, given that different months contain differing numbers of days. For example, the interval “2 months and 10 days” could vary between 69 and 72 days in length, depending on the months involved. Therefore, to avoid unwanted variations in the downstream arithmetic etc., the variable length MONTH<\/code> component may only exist at the lowest significance level in an INTERVAL<\/code> . <\/p>\n

    The value of a MONTH<\/code> field, which is not in the leading field position, is constrained between 0 and 11, in an INTERVAL<\/code> (and not between 1 and 12 as in a DATE<\/code>). You have to round up a dozen months to a year, just you do not describe yourself as “five foot, 1 fourteen inches” on an application. <\/p>\n

    You can assume that your SQL implementation has simple date arithmetic functions, although with different syntax from product to product. The basic functions you need are just those that work with dates: <\/p>\n