{"id":171,"date":"2006-08-16T00:00:00","date_gmt":"2006-10-18T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/robyn-pages-sql-server-datetime-workbench\/"},"modified":"2021-08-16T15:02:25","modified_gmt":"2021-08-16T15:02:25","slug":"robyn-pages-sql-server-datetime-workbench","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/robyn-pages-sql-server-datetime-workbench\/","title":{"rendered":"Robyn Page&#8217;s SQL Server DATE\/TIME Workbench"},"content":{"rendered":"<ul>\n<li>revised: 21th Aug 2013 by Phil Factor<\/li>\n<li>revised: 20th Sep 2012 by Phil Factor<\/li>\n<li>revised: 11th May 2010 by Phil Factor<\/li>\n<\/ul>\n<h2>Using dates, and times in SQL Server: a workbench approach<\/h2>\n<p>I&#8217;d like to encourage you to experiment. One never fails to come up with surprises; for example, I&#8217;d never, before writing this, considered using <code>LIKE<\/code> when searching Date fields, or using the <code>{ t '2:40'}<\/code> in a stored procedure as a literal date. Likewise, I always like to see as many examples as possible in any articles on SQL Server. There is nothing like it for getting ideas going. Formal descriptions are fine for those with strange extra lumps in their brains, but I&#8217;d prefer to see clear explanations peppered with examples! If I have any general advice, it is to use the strengths of the <code>DATETIME<\/code>, <code>DATE<\/code>, <code>DATETIME2<\/code> and <code>DATETIMEOFFSET<\/code> data types and never attempt to bypass their use, by storing dates or times in any other formats such as varchars or integers. I&#8217;ve never come across a circumstance where such a practice has provided any lasting benefit. Also, keep clearly in mind the differences between the presentation format, data-interchange formats and storage formats of dates and times. Remember also the difference between an interval of time and a date.<\/p>\n<h2>Contents<\/h2>\n<ul>\n<li><a href=\"#theprinciples\">The Principles<\/a><\/li>\n<li><a href=\"#thedatatypes\">The DataTypes<\/a><\/li>\n<li><a href=\"#theprinciples\">Inputting Dates<\/a><\/li>\n<li><a href=\"#inputtingtimes\">Inputting Times<\/a><\/li>\n<li><a href=\"#outputtingdates\">Outputting Dates<\/a><\/li>\n<li><a href=\"#manipulatingdates\">Manipulating Dates<\/a><\/li>\n<li><a href=\"#formatttingdates\">Formatting Dates<\/a><\/li>\n<li><a href=\"#calculatingdates\">Calculating Dates<\/a><\/li>\n<li><a href=\"#dateconversions\">Date Conversions<\/a><\/li>\n<li><a href=\"#usingdates\">Using Dates<\/a><\/li>\n<li><a href=\"#morecomplicated\">More complicated Date calculations<\/a><\/li>\n<\/ul>\n<h2 id=\"theprinciples\">The Principles<\/h2>\n<p>Basically, there are three different ways of representing dates, and the same principles apply to other quantities to do with humanity, such as money. Dates can be represented in a human-readable form, the &#8216;presentation&#8217; mode, they can be represented in their storage form, or in their data-interchange form.<\/p>\n<p>We can show this easily:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     Select convert(float,GetDate()) as [the Storage form], --the Storage form (it is actually 4 bytes but we can't show that!)\r\n       convert(varchar(24),GetDate(),113) as [the presentation form],  --the Presentation form\r\n       SYSDATETIMEOffset() as [the data-exchange form] --the Data-exchange form\r\n     \/* when I ran it, it gave\r\n     the Storage form       the presentation form      the data-exchange form\r\n     ---------------------- ------------------------   ----------------------------------\r\n     41503.7162966821       19 Aug 2013 17:11:28:033 2013-08-19 17:11:28.0334411 +01:00\r\n     <\/pre>\n<p>These have entirely different purposes, and it helps to understand the distinction. The storage form is what is kept in the data pages, and what is stored and searched on. Each databases system uses a different format and SQL Server has several! The presentation form is what is presented to us to understand as unambiguously and quickly as possible. The data-interchange form is used to transmit a date and time between systems, languages and time-zones.<\/p>\n<h2>The DataTypes<\/h2>\n<p>The date and time DataTypes on SQL Server 2008 and 2012 \u2013 <code>time<\/code>, <code>date<\/code>, <code>smalldatetime<\/code>, <code>datetime<\/code>, <code>datetime2<\/code> and <code>datetimeoffset<\/code> \u2013 are greatly superior to those of previous versions of SQL Server. Why? Most importantly, we now get a standard means of recording a moment in time that takes into account &#8216;Local time&#8217;. Were the world flat, and the sun going around it, the DateTime format would be ideal. If you need to collect, compare, and aggregate from different locations in different time-zones, then Dates and Times must be recorded in terms of the local time, together with the offset from Coordinated Universal Time (UTC) to local time. For this, the <code>DATETIMEOFFSET<\/code> data type is ideal, and it works much like DateTime.<\/p>\n<p>These date and time functions illustrate what I mean but they won&#8217;t show you the storage form, just the presentation or data-interchange form.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     -- SQL Server 2008 or above only, returns the server's date and time\r\n     Select SYSDATETIME()-- 2013-07-18 18:02:34.10\r\n     \r\n     -- SQL Server 2008 or above only, returns the server's date and time, and offset from UTC\r\n     Select SYSDATETIMEOffset()-- 2013-07-18 18:02:34.1041656 +01:00\r\n     \r\n     --SQL 2005 or above,  the UTC time (Coordinated Universal Time) of the computer.\r\n     Select GETUTCDATE()-- 2013-07-18 17:02:34.103\r\n     \r\n     --same as SYSDATETIME() but returns DateTime rather than datetime2 with less granularity\r\n     Select GETDATE()-- 2013-07-18 18:02:34.103\r\n     \r\n     \/*\r\n     There are now a potentially confusing range of Date and time datatypes.\r\n     time           Accuracy 100 nanoseconds, 3 to 5 bytes\r\n     date           Range: 0001-01-01 to 9999-12-31\r\n     smalldatetime  Range: 1900-01-01 to 2079-06-06 Accuracy: 1 minute, 4 Bytes.\r\n     datetime       Range: 1753-01-01 through 9999-12-31 Accuracy: 0.00333 second, 4 bytes\r\n     datetime2      Range: 0001-01-01 to 9999-12-31, Accuracy: 100 nanosecs  6-8 bytes\r\n     datetimeoffset Range: 0001-01-01 to 9999-12-31, Accuracy: 100 nanosecs 8-10 bytes\r\n     <\/pre>\n<p>Why 1753 for the <code>DATETIME<\/code> range? That&#8217;s when the Gregorian calendar was generally adopted. Any dates before then have a certain ambiguity. The &#8216;Accuracy&#8217; of these times is nothing like the precision of the datatype, which is designed for externally-sourced scientific data. The &#8216;granularity&#8217; of the times you get from the <code>GetDate<\/code> functions is around 15 Ms, but around 1 Ms for the <code>SYS<\/code> versions of the functions.<\/p>\n<p>As a general rule, use <code>datetimeoffset<\/code> where you can, use <code>Time<\/code> and <code>Date<\/code> where the data really is either just a time or just a date, but where you can get away with just using &#8216;local time&#8217; (rare). I can&#8217;t think of a reason for using smalldatetime, and unless you are stuck on SQL Server 2000 or 2005, you&#8217;re very unlikely to need <code>DateTime<\/code> any more. The <code>DateTimeOffset<\/code> can take ten bytes of storage, but <code>DateTimeOffst(2)<\/code> reduces this to 7, and gives you plenty of precision for commercial applications. If you have a reason to be repelled by <code>DateTimeOffset<\/code>, then use <code>DATETIME2<\/code>, which has increased precision, and the full range of dates back to 1 AD.<\/p>\n<p>All these different datatypes work the same way with the date functions. all these functions, for example, pass back different &#8216;storage&#8217; datatypes, but give the same &#8216;presentation type&#8217; date and time as a string in the &#8216;European default&#8217; format (113).<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SELECT CONVERT(CHAR(20),SYSDATETIMEOFFSET(),113)\r\n     SELECT CONVERT(CHAR(20),GETDATE(),113)\r\n     SELECT CONVERT(CHAR(20),SYSDATETIME(),113)\r\n    <\/pre>\n<h2>Inputting dates<\/h2>\n<p>A user will supply dates in a number of formats and, at some point, you will need to get it into one of the Date\/Time DataTypes in the database.<\/p>\n<p>SQL Server 2012 adds a whole lot of functions to convert from parts of a date (e.g. year, month, day, hour, minute, seconds, milliseconds) to a SQL Server date. These are merely conveniences since it was possible to do this anyway, though it was laborious to do so.<\/p>\n<p>These functions are:<\/p>\n<dl class=\"dl--wide\">\n<dt><code>DATEFROMPARTS ( year, month, day )<\/code><\/dt>\n<dd>Returns a date value for the year, month, and day passed as parameters.<\/dd>\n<dt><code>DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) <\/code><\/dt>\n<dd>Returns a datetime2 value for the date and time that you specify, with the specified precision.<\/dd>\n<dt><code>DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )<\/code><\/dt>\n<dd># Returns a datetime value for the specified date and time.<\/dd>\n<dt><code>DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) <\/code><\/dt>\n<dd>Returns a datetimeoffset value for the parts you specify, with the specified offsets and precision.<\/dd>\n<dt><code>SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) <\/code><\/dt>\n<dd>Returns a smalldatetime value for the specified date and time.<\/dd>\n<dt><code>TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )<\/code><\/dt>\n<dd>Returns a time value for the specified time and with the specified precision.<\/dd>\n<\/dl>\n<pre class=\"lang:tsql theme:ssms2012\">     SELECT DATEFROMPARTS ( 2013, 7, 18 ) AS TheDate;\r\n     --TheDate\r\n     ------------\r\n     --2013-07-18   <\/pre>\n<p>Implicit conversion from the presentation format of a data into a storage form can cause problems. Dates can be coerced into the Datatype by assigning string values to variables or columns, but these are usually affected by the <code>DATEFORMAT<\/code> stored for the particular language that is current. The order in which the month (m), day (d), and year (y) is written is different in other countries. US_English (mdy) is different from british (dmy). By explicitly setting the date format you can over-ride this.<\/p>\n<p>You can check your current <code>DATEFORMAT<\/code>, amongst other things by using:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     DBCC USEROPTIONS\r\n      \r\n     --now, to demonstrate that getting your language setting wrong can cause unexpected errors..... \r\n     SET language british \r\n     SELECT @@language, CAST('14\/2\/ 2012' AS DATETIME) -- 2012-02-14 00:00:00.000\r\n     SET language us_english --Changed language setting to us_english.\r\n     SELECT @@language,CAST('14\/2\/ 2012' AS DATETIME) --**ERROR!***\r\n     --keep speaking American, but use the european date format\r\n     SET  DATEFORMAT 'dmy' --to override the language default\r\n     SELECT @@language,CAST('14\/2\/ 2012' AS DATETIME) -- 2012-02-14 00:00:00.000\r\n     SET language british \r\n     SELECT @@language,CAST('14\/2\/ 2012' AS DATETIME) -- 2012-02-14 00:00:00.000\r\n     SET language us_english --Changed language setting to us_english.\r\n     SELECT @@language,CAST('14\/2\/ 2012' AS DATETIME) -- 2012-02-14 00:00:00.000\r\n    <\/pre>\n<p>Any date representation based on words (e.g. febbraio, fevereiro, february) will fail in any other language that uses a different word for a given month. To see the current language settings, use:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     sp_HelpLanguage\r\n     --you can also see the settings for all your available languages here.. \r\n     select DateFormat, DateFirst, alias, months, shortmonths, days from sys.syslanguages\r\n     --and you can change the language settings for your login \r\n     --Go To 'security' in  the object explorer, click on logins and then right-click on your UserName and select Properties. a new dialog box appears and near the base, there is a drop-down list of all the available languages. You can change, if you have the rights, the default language to whatever language you wish\r\n     --or using script, if I wanted my default language to be Noregian....\r\n     ALTER LOGIN [Robyn_Page] WITH DEFAULT_DATABASE=[MyDefault], DEFAULT_LANGUAGE=[Norsk]\r\n      \r\n     \/* To import foreign-language dates, you must change the language setting for the \r\n     connection. \r\n     e.g \r\n     *\/ \r\n     Declare @CurrentLanguage varchar(50)\r\n     Select @CurrentLanguage=@@Language\r\n     SET language Italiano --Changed language setting to Italiano.\r\n     SELECT CAST('10 febbraio 2013' AS DATETIME)\r\n     -- 2013-02-10 00:00:00.000 \r\n     SET language @CurrentLanguage<\/pre>\n<p>Nations have different conventions for representing the date as a numerical string. This is why the default <code>DATEFORMAT<\/code> changes as you change the language.<\/p>\n<p><code>SET DATEFORMAT<\/code> is used to override this for the current connection. It will change the order in which you supply the day, month and year in the date as a string, from the default for your language setting. It can take one of the following strings &#8216;mdy&#8217;, &#8216;dmy&#8217;, &#8216;ymd&#8217;, &#8216;ydm&#8217;, &#8216;myd&#8217;, and &#8216;dym&#8217; (&#8216;ydm&#8217; won&#8217;t work with the <code>DATE<\/code>, <code>DATETIME2<\/code> and <code>DATETIMEOFFSET<\/code> datatypes).<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SET DATEFORMAT dmy;\r\n     DECLARE @datevar datetime\r\n     Set @datevar = '25\/12\/2009 00:00:00'; --implicit conversion\r\n     SELECT @datevar as Christmas;\r\n     \/*\r\n     Christmas\r\n     -----------------------\r\n     2009-12-25 00:00:00.000 *\/<\/pre>\n<p>Whereas if you get the <code>DATEFORMAT<\/code> wrong&#8230;.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SET DATEFORMAT mdy;\r\n     Set @datevar = '25\/12\/2009 00:00:00'; \r\n     SELECT @datevar as Christmas;\r\n     \/*\r\n     Msg 242, Level 16, State 3, Line 15\r\n     The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. *\/\r\n    <\/pre>\n<p>If you need dates to be understood internationally, then you need to use the data-interchange format.<\/p>\n<p><code>DATEFORMAT<\/code> has no effect if you format your dates in a standard way.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SET DATEFORMAT mdy;--set it to something awkward\r\n     Set @DateVar = { d '2012-12-25' } --odbc format\r\n     SELECT @datevar as Christmas;--Works! 2009-12-25 00:00:00.000\r\n     Set @DateVar = '2012-12-25T00:00:00' --ISO 8601 format\r\n     SELECT @datevar as Christmas;--Works! 2009-12-25 00:00:00.000<\/pre>\n<p>So this is the safest way to import date strings, especially when you consider that SQL Server 2008&#8217;s <code>DATE<\/code>, <code>DateTime2<\/code> and <code>Datetimeoffset<\/code> work differently with ANSI SQL Standard strings.<\/p>\n<p>Otherwise SQL Server is fairly accommodating, and will do its best to make sense of a date. All of the following return <code>2012-02-01 00:00:00.000<\/code><\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SET language british \r\n      \r\n     SELECT CAST('1 feb  2012' AS DATETIME)--remember, this is language dependent \r\n     SELECT CAST('1 February  2012' AS DATETIME)--this too \r\n     SELECT CAST('01-02-12' AS DATETIME) \r\n     SELECT CAST(' 2012-02-01 00:00:00.000' AS DATETIME) \r\n     SELECT CAST('1\/2\/12' AS DATETIME) \r\n     SELECT CAST('1.2.12' AS DATETIME) \r\n     SELECT CAST(' 20120201' AS DATETIME) \r\n     \/* from SQL Server 2000 and later you can specify dates in ISO 8601 data-interchange format and these are interpreted the same whatever your DATEFORMAT setting. *\/\r\n     SELECT CAST(' 2012-02-01T00:00:00' AS DATETIME) \r\n     SELECT CAST(' 2012-02-01T00:00:00.000' AS DATETIME) \r\n     --and you'll be able to enter in this format whatever the settings! \r\n     \/* the ANSI standard date uses braces, the marker 'd' to designate the date, and a date string *\/ \r\n     SELECT { d ' 2012-02-01' } \r\n     \/* the ANSI standard datetime uses 'ts' instead of 'd' and adds hours, minutes, \r\n     and seconds to the date (using a 24-hour clock) *\/ \r\n     SELECT { ts ' 2012-02-01 00:00:00' } <\/pre>\n<p>If you use the <code>CONVERT<\/code> function, you can override the <code>DATEFORMAT<\/code> by choosing the correct <code>CONVERT<\/code> style (103 is the British\/French format of dd\/mm\/yyyy (see later for a list of all the styles)<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SET language us_english \r\n     SELECT CONVERT(DateTime,'25\/2\/ 2012',103)        --works fine \r\n     --whereas the 100 style uses the default supplied by the  DATEFORMAT. \r\n     SELECT CONVERT(DateTime,'25\/2\/ 2012',100)        --error! <\/pre>\n<p>The <code>CONVERT<\/code> function gives you a great deal of control over the import of dates in string form, since one can specify the expected format, and is probably the best way of importing dates via a data feed, if the dates aren&#8217;t in the ISO or ODBC format.<\/p>\n<h3>The <code>IsDate<\/code> function<\/h3>\n<p>The <code>IsDate(expression)<\/code> function is used for checking strings to see if they are valid dates. It is language-dependent.<\/p>\n<p><code>ISDATE (Expression)<\/code> returns <code>1<\/code> if the expression is a valid date (according to the language and <code>DATEFORMAT<\/code> mask) and <code>0<\/code> if it isn&#8217;t. The following demonstration uses <code>ISDATE<\/code> to test out the input of strings as dates.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     -- \r\n     SET \r\n     LANGUAGE british SET nocount ON \r\n     -- \r\n     DECLARE \r\n     @DateAsString VARCHAR(20), \r\n     @DateAsDateTime DateTime \r\n     SELECT @DateAsString='2 February  2012' \r\n     SELECT [input]=@DateAsString \r\n     IF (ISDATE(@DateAsString)=1) \r\n     BEGIN \r\n     SELECT \r\n     @DateAsDateTime=@DateAsString \r\n     SELECT [the Date]=COALESCE(CONVERT(CHAR(17),@DateAsDateTime,113),'unrecognised') \r\n     END \r\n     ELSE \r\n     SELECT \r\n     [the Date] ='That was not a date' \r\n<\/pre>\n<h2>Inputting Times<\/h2>\n<p>Times can be input into SQL Server just as easily. Until SQL Server 2008, there were no separate time and date types for storing only times or only dates. It was not really necessary. If only a time is specified when setting a datetime, the date is assumed to be the first of January 1900, the year of the start of the last millennium. If only a date is specified, the time defaults to Midnight. With SQL Server 2008, we now have the <code>DATE<\/code> and <code>TIME<\/code> Data-Types, which make the use of dates and times less idiosyncratic.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SELECT CAST ('17:45' AS DATETIME) -- 1900-01-01 17:45:00.000 \r\n     SELECT CAST ('17:45' AS TIME) -- 17:45:00.0000000 (SQL2008++)\r\n     SELECT CAST ('13:20:25:850' AS DATETIME) -- 1900-01-01 13:20:25.850 \r\n     SELECT CAST ('13:20:25:850' AS TIME) -- 13:20:25.8500000 (SQL2008++)\r\n     SELECT CAST ('3am' AS DATETIME) -- 1900-01-01 03:00:00.000 \r\n     SELECT CAST ('3am' AS TIME) -- 03:00:00.0000000  (SQL2008++)\r\n     SELECT CAST ('10 PM' AS DATETIME) -- 1900-01-01 22:00:00.000 \r\n     SELECT CAST ('10 PM' AS TIME) -- 22:00:00.0000000 (SQL2008++)\r\n     \/* times can be converted back from the DATETIME into the ascii VARCHAR version as follows... *\/\r\n     SELECT CONVERT(VARCHAR(20),GETDATE(),108) -- 15:08:52 \r\n     --108 is the hh:mm:ss CONVERT style (See next section for the complete list) \r\n     SELECT LTRIM(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7))-- 3:10PM \r\n     SELECT LTRIM(RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)) -- 3:19:18:810PM \r\n     --  and so on<\/pre>\n<p>You can input times a different ODBC-standard way (note that the brackets are curly braces.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">    SELECT { t '09:40:00' <\/pre>\n<p>&#8230;which unexpectedly gives 09.40 today, rather than 9:40 on the first of January 1900! (as one might expect from the other time input examples). This is valid in a stored procedure too.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     CREATE PROCEDURE #spExperiment AS \r\n     SELECT \r\n     { t '09:40:00' } \r\n     GO\r\n      \r\n     EXEC #spExperiment <\/pre>\n<h2>Outputting dates<\/h2>\n<p>Dates can be output as strings in a number of ways using the <code>CONVERT<\/code> function together with the appropriate <code>CONVERT<\/code> styles These styles are numeric codes that correspond with the most popular date formats. You get much more versatility with the <code>CONVERT<\/code> function than the <code>CAST<\/code> function.<\/p>\n<p>The <code>CONVERT<\/code> styles override the setting of the <code>DATEFORMAT<\/code> but use the current language setting where the date format uses the name of the month. If you run the following code you will get a result that illustrates all the built-in formats for your particular language settings etc. , using the current date and time<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     DECLARE @types TABLE( \r\n            [2 digit year] INT NULL, \r\n            [4 digit year] INT NOT NULL,  \r\n            name VARCHAR(40)) \r\n     SET LANGUAGE british SET nocount ON \r\n     --Each select statement is followed by an example output string using the style\r\n     INSERT INTO @types   \r\n     Values\r\n          (NULL,100,'Default'),--Oct 17  2012  9:29PM \r\n          (1,101, 'USA'),      --10\/17\/06 or 10\/17\/ 2012 \r\n          (2,102, 'ANSI'),     --06.10.17 or  2012.10.17 \r\n          (3,103, 'British\/French'),--17\/10\/06 or 17\/10\/ 2012 \r\n          (4,104, 'German'),   --17.10.06 or 17.10. 2012 \r\n          (5,105, 'Italian'),  --17-10-06 or 17-10- 2012 \r\n          (6,106, 'dd mon yy'),--17 Oct 06 or 17 Oct  2012  \r\n          (7,107, 'Mon dd, yy'),--Oct 17, 06 or Oct 17,  2012 \r\n          (8,108, 'hh:mm:ss'), --21:29:45 or 21:29:45 \r\n          (NULL,109, 'Default + milliseconds'),--Oct 17  2012  9:29:45:500PM \r\n          (10,110,'USA'),      --10-17-06 or 10-17- 2012 \r\n          (11,111,'JAPAN'),    --06\/10\/17 or  2012\/10\/17 \r\n          (12,112,'ISO'),      --061017 or  20121017   \r\n          (NULL,113,'Europe default(24h) + milliseconds'),--17 Oct  2012 21:29:45:500 \r\n          (14,114,'hh:mi:ss:mmm (24h)'), --21:29:45:500 or 21:29:45:500 \r\n          (NULL,120,'ODBC canonical (24h)'),-- 2012-10-17 21:29:45 \r\n          (NULL,121, 'ODBC canonical (24h)+ milliseconds'),-- 2012-10-17 21:29:45.500 \r\n          (NULL,126, 'ISO8601'),-- 2012-10-17T21:29:45.500 \r\n          (null,127, 'ISO8601 with time zone'), --SQL Server 2005 only! \r\n          (NULL,130, 'Hijri'), --25 ????? 1427  9:33:21:340PM \r\n          (NULL,131, 'Hijri')  --25\/09\/1427  9:29:45:500PM \r\n     SELECT [name], \r\n            [2 digit year]=COALESCE(CONVERT(NVARCHAR(3),[2 digit year]),'-'), \r\n            [example]=CASE WHEN [2 digit year] IS NOT NULL \r\n                      THEN CONVERT(NVARCHAR(30),GETDATE(),[2 digit year]) \r\n                      ELSE '-' END, \r\n            [4 digit year]=COALESCE(CONVERT(NVARCHAR(3),[4 digit year]),'-'), \r\n            [example]=CASE WHEN [4 digit year] IS NOT NULL \r\n                      THEN CONVERT(NVARCHAR(30),GETDATE(),[4 digit year]) \r\n                      ELSE '-' END \r\n     \r\n     FROM @types\r\n    <\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/294-294-robynP1.gif\" alt=\"294-294-robynP1.gif\" \/><\/p>\n<p>Data in SQL Server 2012 onwards can use the format() function that allows you to use .NET&#8217;s formatting facilities, which are rather different to the pre-existing SQL Server methods. \u00a0For dates there are a number of standard formats, as can easily be demonstrated.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true \">DECLARE @Dateformat CHAR(5)\r\nSELECT @Dateformat = 'en-gb'\r\nSET NOCOUNT ON\r\nSELECT FORMAT(GETDATE(), 'd', @Dateformat) AS [Short date pattern.]\r\nSELECT FORMAT(GETDATE(), 'D', @Dateformat) AS [Long date pattern]\r\nSELECT FORMAT(GETDATE(), 'f', @Dateformat) AS [Full date\/time pattern (short time)]\r\nSELECT FORMAT(GETDATE(), 'F', @Dateformat) AS [Full date\/time pattern (long time]\r\nSELECT FORMAT(GETDATE(), 'g', @Dateformat) AS [General date\/time pattern (short time)]\r\nSELECT FORMAT(GETDATE(), 'G', @Dateformat) AS [General date\/time pattern (long time)]\r\nSELECT FORMAT(GETDATE(), 'R', @Dateformat) AS [RFC1123 pattern]\r\nSELECT FORMAT(GETDATE(), 'r', @Dateformat) AS [RFC1123 pattern]\r\nSELECT FORMAT(GETDATE(), 's', @Dateformat) AS [Sortable date\/time pattern]\r\nSELECT FORMAT(GETDATE(), 't', @Dateformat) AS [Short time pattern]\r\nSELECT FORMAT(GETDATE(), 'T', @Dateformat) AS [Long time pattern]\r\nSELECT FORMAT(GETDATE(), 'u', @Dateformat) AS [Universal sortable date\/time pattern]\r\nSELECT FORMAT(GETDATE(), 'U', @Dateformat) AS [Universal full date\/time pattern]\r\nSELECT FORMAT(GETDATE(), 'Y', @Dateformat) AS [Year month pattern]<\/pre>\n<pre class=\"lang:tsql decode:true \">Short date pattern.\r\n  16\/05\/2017\r\n\r\nLong date pattern\r\n  16 May 2017\r\n\r\nFull date\/time pattern (short time)\r\n  16 May 2017 12:15\r\n\r\nFull date\/time pattern (long time\r\n  16 May 2017 12:15:18\r\n\r\nGeneral date\/time pattern (short time)\r\n  16\/05\/2017 12:15\r\n\r\nGeneral date\/time pattern (long time)\r\n  16\/05\/2017 12:15:18\r\n\r\nRFC1123 pattern\r\n  Tue, 16 May 2017 12:15:18 GMT\r\n\r\nRFC1123 pattern\r\n  Tue, 16 May 2017 12:15:18 GMT\r\n\r\nSortable date\/time pattern\r\n  2017-05-16T12:15:18\r\n\r\nShort time pattern\r\n  12:15\r\n\r\nLong time pattern\r\n  12:15:18\r\n\r\nUniversal sortable date\/time pattern\r\n  2017-05-16 12:15:18Z\r\n\r\nUniversal full date\/time pattern\r\n  16 May 2017 11:15:18\r\n\r\nYear month pattern\r\n  May 2017<\/pre>\n<h2>Manipulating dates<\/h2>\n<p>Getting the current date can be done by five functions:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SELECT GETDATE()        --the local date and time \r\n     SELECT GETUTCDATE()     --the UTC or GMT date and time \r\n     SELECT CURRENT_TIMESTAMP--synonymous with GetDate() \r\n     SELECT SYSDATETIME()-- SQL Server 2008 or above only, returns the server's date and time\r\n     SELECT SYSDATETIMEOFFSET()-- SS2008 or above only, server's date and time, and offset from UTC\r\n    <\/pre>\n<p>When extracting parts of a DateTime you have some handy functions that return integers:<\/p>\n<dl class=\"dl--wide\">\n<dt><code>DAY, MONTH, YEAR<\/code><\/dt>\n<dd>Here we get the day, month and year as integers.<\/dd>\n<dt><code>SELECT DAY(GETDATE()),MONTH(GETDATE()),YEAR(GETDATE())<\/code><\/dt>\n<dd>The functions <code>DAY<\/code>, <code>MONTH<\/code> and <code>YEAR<\/code> are shorter than the equivalent <code>DATEPART<\/code> command, but for more general use the <code>DATEPART<\/code> function is more versatile<\/dd>\n<dt><code>SELECT DATEPART(DAY,GETDATE()),DATEPART(MONTH,GETDATE()), DATEPART(YEAR,GETDATE()) <\/code><\/dt>\n<dd>These work just as well with the other date\/Time data types, of course<\/dd>\n<dt><code>DATEADD<\/code><\/dt>\n<dd>\n<p><code>DATEADD<\/code> will actually add a number of years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds, microseconds or nanoseconds to your specified date. The format for this, and the other date-manipulation functions is as follows:<\/p>\n<ul>\n<li>year (yy or yyyy)<\/li>\n<li>quarter (qq or q)<\/li>\n<li>month (mm or m)<\/li>\n<li>week (wk or ww)<\/li>\n<li>Day (dayofyear, dy, y, day, dd, d, weekday or dw)<\/li>\n<li>hour (hh<\/li>\n<li>minute (mi or n),<\/li>\n<li>second (ss or s)<\/li>\n<li>millisecond (ms)<\/li>\n<li>microsecond (mcs) SQL Server 2008 or above only<\/li>\n<li>nanosecond (ns) SQL Server 2008 or above only<\/li>\n<\/ul>\n<p>In these examples we compare the date with the <code>DATEADDed<\/code> date so you can see the effect that the <code>DATEADD<\/code> is having to it<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     \t-- \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(YEAR,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(quarter,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(MONTH,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(dayofyear,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(DAY,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(week,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(weekday,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(hour,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(minute,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(second ,100,'2007-01-01 00:00:00.000') \r\n     \tSELECT '2007-01-01 00:00:00', DATEADD(millisecond,100,'2007-01-01 00:00:00.000') \r\n     \t<\/pre>\n<\/dd>\n<dt><code>DATEDIFF<\/code><\/dt>\n<dd>\n<p><code>DATEDIFF<\/code> returns an integer of the difference between two dates expressed in years, quarters, months, weeks, days, hours, minutes, seconds or milliseconds, microseconds or nanoseconds (it counts the boundaries).<\/p>\n<pre class=\"lang:tsql theme:ssms2012\"> \r\n     \tSELECT DATEDIFF(DAY,'1 feb  2012','1 mar  2012')--28 \r\n     \tSELECT DATEDIFF(DAY,'1 feb 2008','1 mar 2008')--29. Hmm must be a leap year! \r\n     \t<\/pre>\n<p>We will give some practical examples of its use later on in the workbench.<\/p>\n<\/dd>\n<dt><code>DATENAME<\/code><\/dt>\n<dd>\n<p>Unlike <code>DatePart<\/code>, which returns an integer, <code>DATENAME<\/code> returns a NVarchar representing the year, quarter, month, week, day of the week, day of the year, hour, minute, second or millisecond within the date. The month and weekday are given in full from the value in the <code>sysLanguages<\/code> table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SELECT DATENAME (YEAR,GETDATE()) -- 2013 \r\n     SELECT DATENAME (quarter,GETDATE()) --2 \r\n     SELECT DATENAME (MONTH,GETDATE()) --May\r\n     SELECT DATENAME (dayofyear,GETDATE()) --131 \r\n     SELECT DATENAME (DAY,GETDATE()) --11 \r\n     SELECT DATENAME (week,GETDATE()) --20\r\n     SELECT DATENAME (weekday,GETDATE()) --Tuesday \r\n     SELECT DATENAME (hour,GETDATE()) --19 \r\n     SELECT DATENAME (minute,GETDATE()) --21\r\n     SELECT DATENAME (second ,GETDATE()) --52\r\n     SELECT DATENAME (millisecond,GETDATE()) --363 \r\n     SELECT DATENAME (microsecond,SYSDATETIME()) --350734 (SQL Server 2008)\r\n     SELECT DATENAME (nanosecond,SYSDATETIME()) --350734200(SQL Server 2008)\r\n     SELECT DATENAME (TZoffset,SYSDATETIMEOffset()) --+01:00(SQL Server 2008)\r\n     <\/pre>\n<\/dd>\n<dt><code>DATEPART<\/code><\/dt>\n<dd>\n<p><code>DATEPART<\/code> returns an integer representing the part of the date requested in the 1st parameter. You can use year ((yy or yyyy), quarter (qq or q), month (mm or m), dayofyear (dy or y) day (dd or d), week (wk or ww) , weekday (dw),hour (hh), minute (mi or n), second (ss or s), or millisecond (ms) *\/<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">\u00a0SELECT DATEPART(YEAR,GETDATE()) -- 2012\r\n SELECT DATEPART(quarter,GETDATE()) --2\r\n SELECT DATEPART(MONTH,GETDATE()) --5 \r\n SELECT DATEPART(dayofyear,GETDATE()) --131 \r\n SELECT DATEPART(DAY,GETDATE()) --11\r\n SELECT DATEPART(week,GETDATE()) --20 \r\n SELECT DATEPART(ISO_week,GETDATE()) --20 \r\n SELECT DATEPART(weekday,GETDATE()) --3 \r\n SELECT DATEPART(hour,GETDATE()) --19\r\n SELECT DATEPART(minute,GETDATE()) --25 \r\n SELECT DATEPART(second ,GETDATE()) --40 \r\n SELECT DATEPART(millisecond,GETDATE()) --167 \r\n SELECT DATEPART (microsecond,SYSDATETIME()) --166561 (SQL Server 2008)\r\n SELECT DATEPART (nanosecond,SYSDATETIME()) --166561900 (SQL Server 2008)\r\n SELECT DATEPART (TZoffset,SYSDATETIMEOffset())-- 60 (SQL Server 2008)\r\n     \t<\/pre>\n<\/dd>\n<\/dl>\n<h2>Formatting Dates<\/h2>\n<p>Examples of calculating and formatting dates:<\/p>\n<pre class=\"lang:tsql theme:ssms2012 \">     SELECT DATENAME(dw,GETDATE()) --To get the full Weekday name \r\n     SELECT LEFT(DATENAME(dw,GETDATE()),3) --abbreviated Weekday name (MON, TUE, WED etc) \r\n     SELECT DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)-4) --ISO-8601 Weekday number\r\n     SELECT RIGHT('00' + CAST(DAY(GETDATE()) AS VARCHAR),2)--Day of month -- leading zeros \r\n     SELECT CAST(DAY(GETDATE()) AS VARCHAR) --Day of the month without leading space \r\n     SELECT DATEPART(dy,GETDATE()) --day of the year \r\n     SELECT DATEPART(week,GETDATE()) --number of the week in the year \r\n     --ISO-8601 number of the week of the year (monday as the first day of the week) \r\n     --if your language setting does not have monday as day 1\r\n     Declare @Mydatefirst int Select @MyDatefirst=@@DateFirst SET datefirst 1\r\n     SELECT DATEPART(week,GETDATE()) Set datefirst  @MyDatefirst\r\n      \r\n     SELECT DATENAME(MONTH,GETDATE()) --full name of the month \r\n     --Abbreviated name of the month (not true of finnish or french!) \r\n     SELECT LEFT(DATENAME(MONTH,GETDATE()),3)\r\n     --Number of the month with leading zeros \r\n     SELECT RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR),2) \r\n     --two-digit year \r\n     SELECT RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR),2) \r\n     --four-digit year \r\n     SELECT CAST(YEAR(GETDATE()) AS VARCHAR) \r\n     --hour (00-23) \r\n     SELECT DATEPART(hour,GETDATE()) \r\n     --Hour (01-12) \r\n     SELECT LEFT(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7),2) \r\n     --minute \r\n     SELECT DATEPART(minute,GETDATE()) \r\n     --second \r\n     SELECT DATEPART(second,GETDATE()) \r\n     --PM\/AM indicator \r\n     SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),2) \r\n     --time in 24 hour notation \r\n     SELECT CONVERT(VARCHAR(8),GETDATE(),8) \r\n     --Time in 12 hour notation \r\n     SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),7) \r\n     --timezone (or daylight-saving) \r\n     SELECT DATEDIFF(hour, GETDATE(), GETUTCDATE()) \r\n     ----ordinal suffix for the date \r\n     SELECT SUBSTRING('stndrdthththththththththththththththththstndrdthththththththst' \r\n     ,(DATEPART(DAY,GETDATE())*2)-1,2) \r\n     --full date (the variations are infinite. Here is one example \r\n     SELECT DATENAME(dw,GETDATE())+', '+ STUFF(CONVERT(CHAR(11),GETDATE(),106),3,0, \r\n     SUBSTRING('stndrdthththththththththththththththththstndrdthththththththst' \r\n     ,(DATEPART(DAY,GETDATE())*2)-1,2)) \r\n     --e.g. Thursday, 12th Oct  2012\/* \r\n     -- and all the standard dates in the post-2012 FORMAT function\r\n     SELECT FORMAT(GETDATE(), 'd', 'en-us') AS [Short date pattern.] --5\/16\/2017\r\n     SELECT FORMAT(GETDATE(), 'D', 'en-us') AS [Long date pattern] --Tuesday, May 16, 2017\r\n     SELECT FORMAT(GETDATE(), 'f', 'en-us') AS [Full date\/time pattern (short time)] --Tuesday, May 16, 2017 12:25 PM\r\n     SELECT FORMAT(GETDATE(), 'F', 'en-us') AS [Full date\/time pattern (long time] --Tuesday, May 16, 2017 12:25:30 PM\r\n     SELECT FORMAT(GETDATE(), 'g', 'en-us') AS [General date\/time pattern (short time)]--5\/16\/2017 12:25 PM\r\n     SELECT FORMAT(GETDATE(), 'G', 'en-us') AS [General date\/time pattern (long time)]--5\/16\/2017 12:25:30 PM\r\n     SELECT FORMAT(GETDATE(), 'R', 'en-us') AS [RFC1123 pattern] --Tue, 16 May 2017 12:25:30 GMT\r\n     SELECT FORMAT(GETDATE(), 'r', 'en-us') AS [RFC1123 pattern] --Tue, 16 May 2017 12:25:30 GMT\r\n     SELECT FORMAT(GETDATE(), 's', 'en-us') AS [Sortable date\/time pattern] --2017-05-16T12:25:30\r\n     SELECT FORMAT(GETDATE(), 't', 'en-us') AS [Short time pattern] --12:25 PM\r\n     SELECT FORMAT(GETDATE(), 'T', 'en-us') AS [Long time pattern]--12:25:30 PM\r\n     SELECT FORMAT(GETDATE(), 'u', 'en-us') AS [Universal sortable date\/time pattern] --2017-05-16 12:25:30Z\r\n     SELECT FORMAT(GETDATE(), 'U', 'en-us') AS [Universal full date\/time pattern] --Tuesday, May 16, 2017 11:25:30 AM\r\n     SELECT FORMAT(GETDATE(), 'Y', 'en-us') AS [Year month pattern] --May 2017\r\n     <\/pre>\n<h2>Calculating Dates by example<\/h2>\n<pre class=\"lang:tsql theme:ssms2012 \">     -- now \r\n     SELECT GETDATE()\r\n     -- Start of today (first thing) \r\n     SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime) \r\n     --or ...\r\n     select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)\r\n     --or ...\r\n     SELECT cast(cast (GETDATE() as date) as datetime)\r\n        --or even ...\r\n     SELECT CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME)   \r\n     --round the date to a whole second\r\n     SELECT CAST (GetDate() AS DATETIME2(0));\r\n     -- Start of tomorrow (first thing) \r\n     SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime) \r\n     -- Start of yesterday (first thing) \r\n     SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,-1,GETDATE()),113) AS datetime) \r\n     -- Two hours time \r\n     SELECT DATEADD(hour,2,GETDATE()) \r\n     -- Two hours ago \r\n     SELECT DATEADD(hour,-2,GETDATE()) \r\n     -- Same date and time last month \r\n     SELECT DATEADD(MONTH,-1,GETDATE()) \r\n     -- Start of the month \r\n     SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS datetime) \r\n     --or\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)\r\n     -- Start of last month \r\n     SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8) AS datetime) \r\n     --or\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate())-1, 0)\r\n     -- Start of next month \r\n     SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8) AS datetime) \r\n     --or\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate())\r\n     +1, 0) \r\n     --last day this month\r\n     select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))\r\n     select \r\n     EOMONTH(getdate())  --SQL Server 2012 onwards only \r\n     --last sunday in the month\r\n      SELECT DateAdd(DAY,-DateDiff(DAY,0,EOMonth(GetDate()))%7-1, \/* 0=monday, 1=tuesday*\/\r\n\t\t\t\tEOMonth(GetDate()))\r\n     -- Ten minutes ago \r\n     SELECT DATEADD(minute,-10,GETDATE()) \r\n     -- Three weeks ago \r\n     SELECT DATEADD(week,-3,GETDATE()) \r\n     -- Start of the week (this depends on your @@DateFirst setting) \r\n     SELECT DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE()) \r\n     --first (monday, tuesday, wednesday ... sunday in the month\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)+6\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0))\r\n             +@@DateFirst+4)%7 --FIRST monday IN the month\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)+6\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0))\r\n             +@@DateFirst+3)%7 --FIRST tuesday IN the month\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)+6\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0))\r\n             +@@DateFirst+2)%7 --FIRST wednesday IN the month\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)+6\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0))\r\n             +@@DateFirst+1)%7 --FIRST thursday IN the month\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)+6\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0))\r\n             +@@DateFirst+0)%7 --FIRST friday IN the month\r\n     SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)+6\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0))\r\n             +@@DateFirst+6)%7 --FIRST saturday IN the month\r\n     \r\n     --first sunday next month\r\n     SELECT  DATEADD(Month, DATEDIFF(Month, 0, GETDATE()) + 1, 0) + 6 \r\n             - (DATEPART(Weekday, DATEADD(Month, DATEDIFF(Month,0, GETDATE()) + 1, 0))\r\n             + @@DateFirst + 5) % 7 --FIRST sunday IN the following month\r\n     --first sunday last month\r\n     SELECT  DATEADD(Month, DATEDIFF(Month, 0, GETDATE()) - 1, 0) + 6 \r\n             - (DATEPART(Weekday, DATEADD(Month, DATEDIFF(Month,0, GETDATE()) - 1, 0))\r\n             + @@DateFirst + 5) % 7 --FIRST sunday IN the previous month\r\n     --Second sunday last month\r\n     SELECT  DATEADD(Month, DATEDIFF(Month, 0, GETDATE()) - 1, 0) + 13 \r\n             - (DATEPART(Weekday, DATEADD(Month, DATEDIFF(Month,0, GETDATE()) - 1, 0))\r\n             + @@DateFirst + 5) % 7 --Second sunday IN the previous month\r\n     --Third sunday last month\r\n     SELECT  DATEADD(Month, DATEDIFF(Month, 0, GETDATE()) - 1, 0) + 20 \r\n             - (DATEPART(Weekday, DATEADD(Month, DATEDIFF(Month,0, GETDATE()) - 1, 0))\r\n             + @@DateFirst + 5) % 7 --Third sunday IN the previous month\r\n     --first tuesday next month\r\n     SELECT  DATEADD(Month, DATEDIFF(Month, 0, GETDATE()) + 1, 0) + 6 \r\n             - (DATEPART(Weekday, DATEADD(Month, DATEDIFF(Month,0, GETDATE()) + 1, 0))\r\n             + @@DateFirst + 3) % 7 --FIRST tuesday IN the following month\r\n     --Second tuesday next month\r\n     SELECT  DATEADD(Month, DATEDIFF(Month, 0, GETDATE()) + 1, 0) + 13 \r\n             - (DATEPART(Weekday, DATEADD(Month, DATEDIFF(Month,0, GETDATE()) + 1, 0))\r\n             + @@DateFirst + 3) % 7 --FIRST sunday IN the following month\r\n     --Third tuesday next month\r\n     SELECT  DATEADD(Month, DATEDIFF(Month, 0, GETDATE()) + 1, 0) + 20 \r\n             - (DATEPART(Weekday, DATEADD(Month, DATEDIFF(Month,0, GETDATE()) + 1, 0))\r\n             + @@DateFirst + 3) % 7 --second tuesday IN the following month\r\n     --Mother's Day(second Sunday of May)\r\n     SELECT DateAdd(month,4,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0)) + 13\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0,  DateAdd(month,4,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0))), 0))\r\n             +@@DateFirst+5)%7 as [Mothers Day]\r\n     --Father's Day (Third Sunday of June)\r\n     SELECT DateAdd(month,5,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0)) + 20\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0,  DateAdd(month,5,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0))), 0))\r\n             +@@DateFirst+5)%7 as [Fathers Day]\r\n     -- Thanksgiving (Fourth Thursday in November)      \r\n     SELECT DateAdd(month,10,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0)) + 27\r\n             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0,  DateAdd(month,10,DateAdd(Year,DATEDIFF(Year,0, GETDATE()),0))), 0))\r\n             +@@DateFirst+1)%7 as [Thanksgiving Day<\/pre>\n<p>We can, of course, encapsulate all this into a function that will then give us the first, second, third or fourth occurrence of any day of the week in any month of any year perfectly easily.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">        IF OBJECT_ID (N'NthDayOfWeekOfMonth') IS NOT NULL\r\n           DROP FUNCTION NthDayOfWeekOfMonth\r\n        GO\r\n        \r\n        CREATE FUNCTION NthDayOfWeekOfMonth (\r\n        @TheYear CHAR(4), --the year as four characters (e.g. '2014')\r\n        @TheMonth CHAR(3), --in english (Sorry to our EU collegues) e.g. Jun, Jul, Aug\r\n        @TheDayOfWeek CHAR(3), -- one of Mon, Tue, Wed, Thu, Fri, Sat, Sun\r\n        @Nth INT) --1 for the first date, 2 for the second occurence, 3 for the third\r\n        RETURNS DATETIME\r\n        WITH EXECUTE AS CALLER\r\n        AS\r\n        BEGIN\r\n        RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date,'1 '+@TheMonth+' '+@TheYear,113)), 0)+ (7*@Nth)-1\r\n                -(DATEPART (Weekday, DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date,'1 '+@TheMonth+' '+@TheYear,113)), 0))\r\n                +@@DateFirst+(CHARINDEX(@TheDayOfWeek,'FriThuWedTueMonSunSat')-1)\/3)%7\r\n        END        \r\n        GO\r\n        --And now we do some simple assertion tests to check that we haven't messed up!.\r\n        IF (SELECT dbo.NthDayOfWeekOfMonth ('2012','jun','Mon',1))&lt;&gt; '2012-06-04'\r\n            RAISERROR('''NthDayOfWeekOfMonth'' stopped working (1)',16,1)\r\n        IF (SELECT dbo.NthDayOfWeekOfMonth ('2014','Sep','Mon',1))&lt;&gt; '2014-09-01'\r\n            RAISERROR('''NthDayOfWeekOfMonth'' stopped working (2)',16,1)\r\n        IF (SELECT dbo.NthDayOfWeekOfMonth ('2015','Mar','Wed',1))&lt;&gt; '2015-03-04'\r\n            RAISERROR('''NthDayOfWeekOfMonth'' stopped working (3)',16,1)\r\n        IF (SELECT dbo.NthDayOfWeekOfMonth ('1987','Dec','Sun',1))&lt;&gt; '1987-12-06'\r\n            RAISERROR('''NthDayOfWeekOfMonth'' stopped working (4)',16,1)\r\n        --thanksgiving (4th thursday in november)\r\n        IF (SELECT dbo.NthDayOfWeekOfMonth ('2014','Nov','Thu',4))&lt;&gt; '2014-11-27'\r\n            RAISERROR('''NthDayOfWeekOfMonth'' stopped working (5)',16,1)\r\n        IF (SELECT dbo.NthDayOfWeekOfMonth ('2015','Nov','Thu',4))&lt;&gt; '2015-11-26'\r\n            RAISERROR('''NthDayOfWeekOfMonth'' stopped working (6)',16,1)\r\n        --Father's Day UK (Third Sunday of June)\r\n        IF (SELECT dbo.NthDayOfWeekOfMonth ('2014','Jun','Sun',3))&lt;&gt; '2014-6-15'\r\n            RAISERROR('''NthDayOfWeekOfMonth'' stopped working (7)',16,1)\r\n        IF (SELECT dbo.NthDayOfWeekOfMonth ('2015','Jun','Sun',3))&lt;&gt; '2015-6-21'\r\n            RAISERROR('''NthDayOfWeekOfMonth'' stopped working (8)',16,1) \r\n     GO\r\n     --What's the current time?\r\n     SELECT CAST (GETDATE() AS TIME)\r\n     SELECT\r\n     CAST (' 2012-10-26 12:12:12.8888888' AS DATETIME2(5)), -- convert date to include nanoseconds\r\n     CAST (' 2012-10-26 12:12:12.8888888' AS DATETIME2(0)); -- whole seconds\r\n     \r\n     -- Start of tomorrow (first thing)\r\n     SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)\r\n     --or ...\r\n     SELECT CAST (CEILING(CAST(GetDate() AS FLOAT)) AS DATETIME)\r\n     -- Start of yesterday (first thing)\r\n     SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,-1,GETDATE()),113) AS datetime)\r\n     --first day of the current quarter\r\n     select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)\r\n      \r\n     --calculating the start of other quarters\r\n     SELECT  DATEADD(qq, DATEDIFF(qq,0,GETDATE())-1, 0) AS [start of previous quarter],\r\n             DATEADD(qq, DATEDIFF(qq,0,GETDATE()), 0) AS [start of this quarter], \r\n             DATEADD(qq, DATEDIFF(qq,0,GETDATE())+1, 0) AS [start of next quarter], \r\n             DATEADD(qq, DATEDIFF(qq,0,GETDATE())+2, 0) AS [start of quarter after next]    \r\n      \r\n     -- last year \r\n     SELECT DATEADD(YEAR,-1,GETDATE()) \r\n     --final day of previous year\r\n     select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))\r\n     -- new year, this year \r\n     SELECT CAST('01 Jan'+ DATENAME(YEAR,GETDATE()) AS datetime) \r\n     --or\r\n     select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)\r\n     -- new year, last year \r\n     SELECT CAST('01 Jan'+ DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())) AS datetime) \r\n     -- Last moment of this year\r\n     select dateadd(ms,-1, CAST (DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0) AS DateTime2))\r\n     -- next christmas \r\n     SELECT CASE WHEN DATEPART(dy,GETDATE())&lt;DATEPART(dy,'25 Dec'+ + DATENAME(YEAR,GETDATE())) \r\n     THEN CAST('25 Dec'+ + DATENAME(YEAR,GETDATE()) AS datetime) \r\n     ELSE CAST('25 Dec'+ CAST(DATEPART(YEAR,GETDATE())+1 AS VARCHAR) AS datetime) END\r\n     <\/pre>\n<h2>Date Conversions<\/h2>\n<p>When converting from SQL Server dates to Unix timestamps, the dates are rounded to the nearest second (Unix timestamps are only accurate to the nearest second) SQL Server date to Unix timestamp (based on seconds since standard epoch of 1\/1\/1970).<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SELECT DATEDIFF(second,'1\/1\/1970',GETDATE()) -- UNIX timestamp to SQL Server \r\n     SELECT DATEADD(second, 1160986544, '1\/1\/1970') \r\n<\/pre>\n<p>The newer datatypes can give some fascinating information. Here is an instant way of finding what the current time and date is, in a variety of parts of the world.<\/p>\n<pre class=\"lang:tsql theme:ssms2012\"> \r\n     DECLARE @Timezones TABLE( timezone CHAR(6), Place VARCHAR(30))\r\n     INSERT INTO @Timezones (timezone, Place) \r\n     VALUES\r\n        ('-10:00', 'Hawaii'),\r\n        ('-09:00', 'Alaska'),\r\n        ('-08:00', 'Los Angeles'),\r\n        ('-07:00', 'Arizona'),\r\n        ('-06:00', 'Chicago'),\r\n        ('-05:00', 'New York'),\r\n        ('-03:00', 'Rio De Janeiro'),\r\n        ('-01:00', 'Azores'),\r\n        ('-00:00', 'London'),\r\n        ('+01:00', 'Berlin'),\r\n        ('+02:00', 'Cairo'),\r\n        ('+03:00', 'Moscow'),\r\n        ('+04:00', 'Dubai'),\r\n        ('+05:00', 'Islamabad'),\r\n        ('+05:30', 'Bombay'),\r\n        ('+07:00', 'Bangkok'),\r\n        ('+08:00', 'Beijing'),\r\n        ('+09:00', 'Tokyo'),\r\n        ('+10:00', 'Sydney'),\r\n        ('+12:00', 'Auckland')\r\n     SELECT\r\n       Place, CONVERT(CHAR(20), SWITCHOFFSET(SYSDATETIMEOFFSET( ), timezone), 113)\r\n     FROM @timezones ORDER BY  place\r\n    <\/pre>\n<p class=\"MsoNormal\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/294-294-robynP2.gif\" alt=\"294-294-robynP2.gif\" \/><\/p>\n<p>We&#8217;ve put a fuller version of this script in the downloads at the bottom of the article.<\/p>\n<h2>Using dates<\/h2>\n<p>When storing dates, always use one of the date\/time data types. Do not feel tempted to use tricks such as storing the year, month or day as integers, with the idea that this will help retrieval and aggregation for reports. It never does.<\/p>\n<p>if you use the <code>DATETIMEOFFSET<\/code>, you are reasonably future-proof as you store dates as the UTC date together with the offset. This means that you can do dime-and-date calculations on data, even if it has been taken from more than one time zone.<\/p>\n<p>The manipulation of the date\/time data types is so critical to SQL Server&#8217;s performance that it is highly optimised. indexes based on date\/time data type work very well, sort properly, and allow fast partitioning on a variety of criteria such as week, month, year-to-date and so on.<\/p>\n<p>If, for example, you store a list of purchases by date in a table such as <code>PURCHASES<\/code> you can find the sum for the previous week by:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     SELECT SUM(total) FROM purchases \r\n     WHERE purchaseDate BETWEEN DATEADD(week,-1,GETDATE()) AND GETDATE() \r\n      \r\n     --this will pick up an index on PurchaseDate\r\n     --what about sales since the start of the week \r\n     \r\n     SELECT SUM(total) FROM purchases \r\n     WHERE purchaseDate BETWEEN \r\n     DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE()) AND GETDATE() --Want a daily total? \r\n      \r\n     SELECT CONVERT(CHAR(11),PurchaseDate,113), \r\n     SUM(total) FROM purchases \r\n     GROUP BY CONVERT(CHAR(11),PurchaseDate,113) \r\n     ORDER BY MIN(PurchaseDate) \r\n     \r\n     --Or to find out which days of the week were the best? \r\n     SELECT DATENAME(dw,PurchaseDate), \r\n     [No. Purchases]=COUNT(*), [revenue]=SUM(total) FROM [purchases] \r\n     GROUP BY DATENAME(dw,PurchaseDate), DATEPART(dw,PurchaseDate) \r\n     ORDER BY DATEPART(dw,PurchaseDate) \r\n      \r\n     --Want a week by week total? \r\n     SELECT 'Week '+DATENAME(week,purchaseDate)+' '+DATENAME(YEAR,purchaseDate), \r\n     SUM(total) FROM purchases \r\n     GROUP BY 'Week '+DATENAME(week,purchaseDate)+' '+DATENAME(YEAR,purchaseDate) \r\n     ORDER BY MIN(InsertionDate)\r\n     \r\n     --(you'd miss weeks where nothing was purchased if you did it this way.) \r\n     \/* The LIKE expression can be used for searching for datetime values. \r\n     If, for example, one wants to search for all purchases done at 9:40, one can find \r\n     a match by the clause WHERE purchaseDate LIKE '%9:40%'. *\/ \r\n     SELECT * FROM [purchases] \r\n     WHERE purchaseDate LIKE '%9:40%' \r\n     \r\n     --or all purchases in the month of february \r\n     SELECT COUNT(*) FROM [purchases] \r\n     WHERE purchaseDate LIKE '%feb%'\r\n     \r\n     --all purchases where there is a 'Y' in the month (matches only May!) \r\n     SELECT DATENAME(MONTH, insertionDate), COUNT(*) FROM [purchases] \r\n     WHERE purchaseDate LIKE '%y%' \r\n     GROUP BY DATENAME(MONTH, purchaseDate) \r\n    <\/pre>\n<p>This <code>LIKE<\/code> trick is of limited use and should be used with considerable caution as it uses artifice to get its results.<\/p>\n<h2 id=\"morecomplicated\">More complicated Date calculations<\/h2>\n<p>So now some more complicated stuff. Here is how you calculate Easter:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     Create FUNCTION Easter ( @input_date DATETIME )\r\n     \/*\r\n     calculates the date of easter for the given year. This calculation is the current one approved by the vatican. It differs from the greek orthodox. \r\n     *\/\r\n     RETURNS DATETIME\r\n         WITH EXECUTE AS CALLER\r\n     AS BEGIN\r\n         DECLARE @y INTEGER,\r\n             @dy INTEGER,\r\n             @easter VARCHAR(20),\r\n             @easter_month INTEGER,\r\n             @easter_day INTEGER ;\r\n      \r\n         SET @y = DATEPART(YEAR, @input_date) ;\r\n      \r\n         SET @dy = ( ( 19 * ( @y % 19 ) + ( @y \/ 100 ) - ( ( @y \/ 100 ) \/ 4 ) - ( ( ( @y \/ 100 ) - ( ( ( @y \/ 100 ) + 8 ) \/ 25 ) + 1 ) \/ 3 ) + 15 ) % 30 ) + ( ( 32 + 2 * ( ( @y \/ 100 ) % 4 ) + 2 * ( ( @y % 100 ) \/ 4 ) - ( ( 19 * ( @y % 19 ) + ( @y \/ 100 ) - ( ( @y \/ 100 ) \/ 4 ) - ( ( ( @y \/ 100 ) - ( ( ( @y \/ 100 ) + 8 ) \/ 25 ) + 1 ) \/ 3 ) + 15 ) % 30 ) - ( ( @y % 100 ) % 4 ) ) % 7 ) - 7 * ( ( ( @y % 19 ) + 11 * ( ( 19 * ( @y % 19 ) + ( @y \/ 100 ) - ( ( @y \/ 100 ) \/ 4 ) - ( ( ( @y \/ 100 ) - ( ( ( @y \/ 100 ) + 8 ) \/ 25 ) + 1 ) \/ 3 ) + 15 ) % 30 ) + 22 * ( ( 32 + 2 * ( ( @y \/ 100 ) % 4 ) + 2 * ( ( @y % 100 ) \/ 4 ) - ( ( 19 * ( @y % 19 ) + ( @y \/ 100 ) - ( ( @y \/ 100 ) \/ 4 ) - ( ( ( @y \/ 100 ) - ( ( ( @y \/ 100 ) + 8 ) \/ 25 ) + 1 ) \/ 3 ) + 15 ) % 30 ) - ( ( @y % 100 ) % 4 ) ) % 7 ) ) \/ 451 ) + 114 ;\r\n      \r\n         SET @easter_month = @dy \/ 31 ;\r\n         SET @easter_day = ( @dy % 31 ) + 1 ;\r\n      \r\n     -- assumes proprietary, non-ANSI local temporal format \r\n         SET @easter = CASE @easter_month\r\n                         WHEN 3 THEN 'Mar'\r\n                         ELSE 'Apr'\r\n                       END ; \r\n         SET @easter = @easter + SPACE(1) + CAST(@easter_day AS VARCHAR(2)) + ', '\r\n             + CAST(@y AS VARCHAR(4)) \r\n         RETURN CAST(@easter AS DATETIME) ;\r\n         \r\n        END ;\r\n     \/* and you can use it to list out the dates of easter for a few years and check that the routine works *\/\r\n     DECLARE @Easter TABLE\r\n         (\r\n           year INT,\r\n           Easter DATETIME\r\n         )\r\n     DECLARE @TheYear DATETIME\r\n     SELECT  @TheYear = DATEADD(year, -15, CURRENT_TIMESTAMP)\r\n     WHILE DATEDIFF(year, @TheYear, '1 Jun 2020') &gt; 0\r\n         BEGIN\r\n             INSERT  INTO @Easter ( Year, Easter )\r\n                     SELECT  DATEPART(year, @TheYear),\r\n                             dbo.easter(@TheYear)\r\n             SELECT  @TheYear = DATEADD(year, 1, @TheYear)\r\n         END\r\n     SELECT  year,\r\n             CONVERT(CHAR(11), Easter, 113) AS [Easter Day]\r\n     FROM    @easter    \r\n     \r\n     -- A lot of dates are calculated from Easter such as\r\n     -- Ash Wednesday\r\n     Select dateAdd(day,-46,dbo.easter(GetDate())) --Ash Wednesday\r\n     Select dateAdd(day,-47,dbo.easter(GetDate())) --Shrove Tuesday or Mardi Gras\r\n     Select dateAdd(day,-7,dbo.easter(GetDate())) --Palm Sunday\r\n     Select dateAdd(day,-3,dbo.easter(GetDate())) --Maundy Thursday\r\n     Select dateAdd(day,-2,dbo.easter(GetDate())) --Good Friday\r\n     Select dateAdd(week,7,dbo.easter(GetDate())) --Pentecost\r\n     Select dateAdd(day,37,dbo.easter(GetDate())) --Ascension\r\n     Select dateAdd(day,56,dbo.easter(GetDate())) --Trinity Sunday\r\n     Select dateAdd(day,60,dbo.easter(GetDate())) --Corpus Christi\r\n    <\/pre>\n<p>And now for something slightly different, how could one calculate the sort of calculator that appears when you need to input a date (This can be used for some types of management reports where daily sales volumes need to be tracked.)<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/294-img73.jpg\" alt=\"294-img73.jpg\" \/><\/p>\n<pre class=\"lang:tsql theme:ssms2012\">     create procedure spCalendar\r\n     --draw a calendar as a result set. you can specify the month if you wwant\r\n     @Date datetime=null--any date within the month that you want to calendarise.\r\n     \/*\r\n     For Novermber 2013 it gives...\r\n      \r\n     Mon  Tue  Wed  Thu  Fri  Sat  Sun\r\n     ---- ---- ---- ---- ---- ---- ----\r\n                         1    2    3 \r\n     4    5    6    7    8    9    10\r\n     11   12   13   14   15   16   17\r\n     18   19   20   21   22   23   24\r\n     25   26   27   28   29   30\r\n      \r\n      \r\n     eg. spCalendar '1 Jan 2006'\r\n     Execute spCalendar '1 sep 2013'\r\n     Execute spCalendar '1 nov 2013'\r\n     Execute spCalendar '28 feb 2008'\r\n     Execute spCalendar '1 mar 1949'\r\n     Execute spCalendar '10 jul 2020'\r\n      \r\n     *\/\r\n     as\r\n     Set nocount on\r\n     --nail down the start of the week\r\n     Declare @MonthLength int --number of days in the month\r\n     Declare @MonthStartDW int --the day of the week that the month starts on\r\n     --if no date is specified, then use the current date\r\n     Select @Date='01 '+substring(convert(char(11),coalesce(@date,GetDate()),113),4,8)\r\n     --get the number of days in the month and the day of the week that the month starts on\r\n     Select @MonthLength=datediff(day,convert(char(11),@Date,113),convert(char(11),DateAdd(month,1,@Date),113)),\r\n     @MonthStartDW=((Datepart(dw,@date)+@@DateFirst-3) % 7)+1\r\n      \r\n     Select\r\n     [Mon]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end),\r\n     [Tue]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end),\r\n     [Wed]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end),\r\n     [Thu]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end),\r\n     [Fri]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end),\r\n     [Sat]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end),\r\n     [Sun]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end)\r\n     from\r\n     (--roll out the day number and week number so you can then do a pivot table of the results\r\n     Select [day]=DayNo.Number, [week]=Weekno.number,\r\n     [monthDate]=(DayNo.Number + ((Weekno.number-1)*7))-@MonthStartDW\r\n     from (VALUES (1),(2),(3),(4),(5),(6),(7)) AS DayNo(number)\r\n     cross join \r\n      (VALUES (1),(2),(3),(4),(5),(6)) AS Weekno(number)\r\n      \r\n     )f\r\n     group by [week]--so that each week is on a different row\r\n     having max(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)&gt;0\r\n     or (week=1 and sum(MonthDate)&gt;-21)\r\n     --take out any weeks on the end without a valid day in them!\r\n    <\/pre>\n<p class=\"note\">If you spot a mistake or an omission, or you think of another useful date calculation, please let the editor know and we&#8217;ll get the article updated.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>To celebrate the seventh anniversary of Robyn Page&#8217;s classic workbench, Phil brings it right up to date. What starts as a gentle introduction to the use of dates in SQL Server ends up with some unusual tricks in creating calendars and calculating Mardi Gras. &hellip;<\/p>\n","protected":false},"author":221812,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4467,4465,4463,4464,4462,4149,4466,4150,4151,4461,4460],"coauthors":[6814],"class_list":["post-171","post","type-post","status-publish","format-standard","hentry","category-learn","tag-date-arithmetic","tag-dateadd","tag-datename","tag-datepart","tag-getdate","tag-learn-sql-server","tag-manipulating-dates","tag-sql","tag-sql-server","tag-sql-server-dates","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/171","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221812"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=171"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/171\/revisions"}],"predecessor-version":[{"id":74809,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/171\/revisions\/74809"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=171"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}