Excel and SQL Server epoch dates and treatment of the year 1900

Excel stores a date and time as a number where the integer part represents the number of days since 1900-01-00 and the fractional part represents the time as a fraction of a 24 hour day e.g. 18/03/2014 21:09 as the number 41716.88125. Now the integer part is actually out by 1 day as Excel incorrectly assumes that the year 1900 is a leap year and so counts 1900-02-29 as a day.

So, if we calculate the number of days between the Epoch date and a test date we can see that 1900-02-29 is counted as a day:

Epoch Date	Test Date	Difference in Days
00/01/1900 00:00	28/02/1900 00:00	59
00/01/1900 00:00	01/03/1900 00:00	61

Now if we import the number representing a datetime in Excel into SQL Server and then in SQL Server convert it to a datetime, we can expect to be out by 2 days for dates after 1900-02-28: 1 day because SQL Server doesn’t count 1900-02-29 as a day plus another day because SQL Server uses 1900-01-01 as its Epoch date:

SELECT CONVERT(DATETIME, 0);
--returns 1900-01-01 00:00:00.000
SELECT DATEDIFF(DAY, CONVERT(DATETIME, 0), CONVERT(DATETIME, '1900-02-28'));
--returns 58
SELECT DATEDIFF(DAY, CONVERT(DATETIME, 0), CONVERT(DATETIME, '1900-03-01'));
--returns 59

T-SQL: Convert a 7 digit date to date type

Suppose you have a date in the format yyyyddd where yyyy is the year and ddd is the day in the year (1 to 365 or 366 in a leap year), and you want to convert it to date type. The sql below performs this conversion.

--http://www.c-sharpcorner.com/Forums/Thread/201996/cast-7-digit-string-format-date-into-date-in-sql.aspx
DECLARE @date INT = 2011211;
 
SELECT DATEADD(DAY, CONVERT(INT,SUBSTRING(CONVERT(CHAR(7),@date),5,3)) - 1, CONVERT(DATE,SUBSTRING(CONVERT(CHAR(7),@date),1,4) + '-01-01'));

The conversion essentially has three steps:

Convert to string. Get the year part (first 4 characters). Add ‘-01-01’. Convert to date.
Convert to string. Get the day number part (last 3 characters). Convert to int. Subtract 1 (to remove the 1st Jan).
Add the number of days calculated in step 2 to the date calculated in step 1.

E.g. the query

SELECT
      @date AS SevenDigitDate
      , CONVERT(DATE,SUBSTRING(CONVERT(CHAR(7),@date),1,4) + '-01-01') AS Step1
      , CONVERT(INT,SUBSTRING(CONVERT(CHAR(7),@date),5,3)) - 1 AS Step2
      , DATEADD(DAY, CONVERT(INT,SUBSTRING(CONVERT(CHAR(7),@date),5,3)) - 1, CONVERT(DATE,SUBSTRING(CONVERT(CHAR(7),@date),1,4) + '-01-01')) AS Step3;

returns

SevenDigitDate	Step1	Step2	Step3
2011211	2011-01-01	210	2011-07-30

T-SQL: Calculate age in years from date of birth

On stackoverflow I found the following query to calculate a person’s age in years from their date of birth:

DECLARE @date_of_birth date = '2003-01-21';
DECLARE @todays_date date = '2014-03-01';

SELECT
    (CONVERT(INT,CONVERT(VARCHAR(8),@todays_date,112)) - CONVERT(INT,CONVERT(VARCHAR(8),@date_of_birth,112)))/10000;

Breaking down this query, we can see how it works:

DECLARE @date_of_birth date = '2003-01-21';
DECLARE @todays_date date = '2014-03-01';
 
SELECT
    @date_of_birth
    , @todays_date
    , CONVERT(INT,CONVERT(VARCHAR(8),@date_of_birth,112))
    , CONVERT(INT,CONVERT(VARCHAR(8),@todays_date,112))
    , CONVERT(INT,CONVERT(VARCHAR(8),@todays_date,112)) - CONVERT(INT,CONVERT(VARCHAR(8),@date_of_birth,112))
    , (CONVERT(INT,CONVERT(VARCHAR(8),@todays_date,112)) - CONVERT(INT,CONVERT(VARCHAR(8),@date_of_birth,112)))/10000;

which returns:

2003-01-21

2014-03-01

20030121

20140301

110180

For those born on a Leap day, this query assumes their birthday is on the 1st March in non-Leap years:

DECLARE @dates_table TABLE (
    Date_of_Birth DATE NOT NULL
    ,Todays_Date DATE NOT NULL);
 
INSERT INTO @dates_table
VALUES ('2000-03-01','2013-03-01'),('2000-03-01','2013-11-07')
,('2000-12-01','2013-11-07'),('2000-11-06','2013-11-07')
,('2000-11-08','2013-11-07'),('2004-02-29','2012-01-28')
,('2004-02-29','2012-02-28'),('2004-02-29','2012-02-29')
,('2004-02-29','2012-03-01'),('2004-02-29','2013-02-28')
,('2004-02-29','2013-03-01'),('2004-02-29','2014-02-28')
,('2004-02-29','2014-03-01');
  
SELECT
    Date_of_Birth
    , Todays_Date
    , CONVERT(INT,CONVERT(VARCHAR(8),Date_of_Birth,112))
    , CONVERT(INT,CONVERT(VARCHAR(8),Todays_Date,112))
    , CONVERT(INT,CONVERT(VARCHAR(8),Todays_Date,112)) - CONVERT(INT,CONVERT(VARCHAR(8),Date_of_Birth,112))
    , (CONVERT(INT,CONVERT(VARCHAR(8),Todays_Date,112)) - CONVERT(INT,CONVERT(VARCHAR(8),Date_of_Birth,112)))/10000
FROM
    @dates_table;

which returns:

2000-03-01	2013-03-01	20000301	20130301	130000	13
2000-03-01	2013-11-07	20000301	20131107	130806	13
2000-12-01	2013-11-07	20001201	20131107	129906	12
2000-11-06	2013-11-07	20001106	20131107	130001	13
2000-11-08	2013-11-07	20001108	20131107	129999	12
2004-02-29	2012-01-28	20040229	20120128	79899	7
2004-02-29	2012-02-28	20040229	20120228	79999	7
2004-02-29	2012-02-29	20040229	20120229	80000	8
2004-02-29	2012-03-01	20040229	20120301	80072	8
2004-02-29	2013-02-28	20040229	20130228	89999	8
2004-02-29	2013-03-01	20040229	20130301	90072	9
2004-02-29	2014-02-28	20040229	20140228	99999	9
2004-02-29	2014-03-01	20040229	20140301	100072	10

T-SQL: Convert dates in int format yyyymmdd to date

Suppose you have an int column which contains dates in the format yyyymmdd, e.g. 20140228, and you want to convert them to the date data type. You might try

SELECT CONVERT(DATE,20140228,112)

Here the style 112 says the input style is yyyymmdd. This fails with the error message

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to date is not allowed.

To solve this, first convert the int to a char

SELECT CONVERT(DATE,CONVERT(CHAR(8),20140228),112)

T-SQL: Split string using nodes() method

Suppose you want to split a delimited string. On stackoverflow Split string in SQL, a user defined function was suggested which uses the nodes() method (refer to the post T-SQL: nodes() method). The basic principle is

Create a new string (@xml) by (i) adding an opening tag at the start, (ii) replacing the delimiters with a closing tag and opening tag, and (iii) adding a closing tag at the end.
Then use nodes() and value() to return the content enclosed in each element

--http://stackoverflow.com/questions/2647/split-string-in-sql
IF OBJECT_ID('split', 'TF') IS NOT NULL
    DROP FUNCTION split
GO

CREATE FUNCTION [dbo].[split](
      @delimited NVARCHAR(MAX),
      @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
 
  INSERT INTO @t (val)
  SELECT r.value('.','varchar(MAX)') AS item
  FROM @xml.nodes('/t') AS records(r)
  RETURN
END;
GO
 
SELECT * FROM dbo.split('Hello Mr John Smith',' ');

which returns

id	val
1	Hello
2	Mr
3	John
4	Smith

T-SQL: nodes() method

I recently had to use the nodes() method and found the article The XML Methods in SQL Server very useful. The following examples are inspired by this article.

DECLARE @quiz XML;
SET @quiz =
   '<quiz>
            <question>Clean, Jerk, and Snatch are terms used in which activity?</question>
            <answer>Weightlifting</answer>
            <question>How many men have walked on the moon: 4, 8, or 12?</question>
            <answer>12</answer>
            <question>What does a sommelier do?</question>
            <answer>Taste wine</answer>
   </quiz>';
 
SELECT
      Question.value('.','varchar(500)') AS 'Question'
FROM
      @quiz.nodes('/quiz/question') AS Quiz(Question);

The above SELECT returns the content of all the question elements:

Clean, Jerk, and Snatch are terms used in which activity?

How many men have walked on the moon: 4, 8, or 12?

What does a sommelier do?

The following SELECT returns just the second question:

SELECT
      Question.value('(./question)[2]','varchar(500)') AS 'Question'
FROM
      @quiz.nodes('/quiz') AS Quiz(Question);