title: How do I populate my Date Dimension Table in SQL Server so as to get a column with this specific value?
body: <p>I have the following query that runs in SQL Server 2014 to populate a table called DateDimension.</p>
  
  <pre><code>DECLARE @startdate datetime
     DECLARE @enddate datetime
  SET @startdate = '01/01/2000'
     SET @enddate = '12/31/2018'
  DECLARE @loopdate datetime
     SET @loopdate = @startdate
  WHILE @loopdate &lt;= @enddate
     BEGIN
  INSERT INTO DateDimension VALUES (
     Day(@loopdate),
     Month(@loopdate), 
     Year(@loopdate), 
     CASE WHEN Month(@loopdate) IN (1, 2, 3) THEN 3 -- Since Financial Year starts in July, January to March is considered as Quarter 3
     WHEN Month(@loopdate) IN (4, 5, 6) THEN 4
     WHEN Month(@loopdate) IN (7, 8, 9) THEN 1
     WHEN Month(@loopdate) IN (10, 11, 12) THEN 2
     END,
     @loopdate,
     CONVERT(VARCHAR(10),@loopdate,111) 
     ) 
  
     SET @loopdate = DateAdd(d, 1, @loopdate)
     END
  </code></pre>
  
  <p>Extract of current table, after running the above query is as follows:</p>
  
  <pre><code>id  Day Month   Year quarter    date        datevarchar
  1   1   1       2000    3    2000-01-01       1/1/2000
  2   2   1       2000    3    2000-01-02       1/2/2000
  3   3   1       2000    3    2000-01-03       1/3/2000
  4   4   1       2000    3    2000-01-04       1/4/2000
  5   5   1       2000    3    2000-01-05       1/5/2000
  </code></pre>
  
  <p>I need the "quarter" column to show its values as below:</p>
  
  <pre><code>   quarter
    Qr 3 2000
  </code></pre>
  
  <p>It would be even nicer if the "quarter" column be left as-is and a new column added to show what I'm after. How can I do this?</p>