SQL Server Date Formats

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers.

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1

HH:MIAM (or PM)

Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +

milliseconds

SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical

(with milliseconds)

SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM
Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), ‘/’, ‘-‘) AS [YY-MM-DD]
99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), ‘/’, ‘-‘) AS [YYYY-MM-DD]
1999-01-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]

08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12
Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 20061
Mon YYYY1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1
Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1
DD Month1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month] 11 September 1
Month DD1 SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1
DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1
DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]

12/92
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]

92/12
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) AS [MMDDYY] 122506
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) AS [MMDDYYYY] 12252006
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) AS [DDMMYY] 240702
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) AS [DDMMYYYY] 24072002
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY] Sep-02 1
Mon-YYYY1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY] Sep-2002 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY] 25-Dec-05 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY] 25-Dec-20051

1 To make the month name in upper case, simply use the UPPER string function.

How to generate Subtotal, Total and Grand Total columns?

Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate the architecting of subtotal, total and grand total queries. Grand Total definition- the sum of the TOTALs of several groups of numbers.

—SQL grand total simple, quick syntax – isnull is used to make report readable

SELECT   isnull(convert(VARCHAR,ProductID),‘GRAND TOTAL’) AS ProductID,

         sum(Quantity)                                    AS ‘Total In Inventory’

FROM     AdventureWorks2008.Production.ProductInventory

WHERE    ProductID >= 800

GROUP BY ProductID WITH ROLLUP

ORDER BY ProductID;

GO

/* Partial results

ProductID         Total In Inventory

996               970

997               153

998               155

999               194

GRAND TOTAL       65709

*/

————

— SQL sales subtotal, total, grand total – simple logic – month, year, all times

SELECT   isnull(convert(VARCHAR,Year(OrderDate)),‘GRAND TOTAL’) AS YEAR,

         CASE

           WHEN YEAR(OrderDate) IS NOT NULL

           THEN isnull(convert(VARCHAR,Month(OrderDate)),‘YEARLY TOTAL’)

           ELSE

         END AS MONTH,

— SQL currency formatting

         ‘$’+CONVERT(varchar,SUM(TotalDue),1)     AS [MONTHLY SALES SUBTOTAL]

FROM     AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY Year(OrderDate),

         Month(OrderDate) WITH ROLLUP

ORDER BY YEAR,

         MONTH

/*  Partial results

 

YEAR              MONTH                   MONTHLY SALES SUBTOTAL

2004              4                       $4,722,890.74

2004              5                       $6,518,825.23

2004              6                       $6,728,034.99

2004              7                       $56,178.92

2004              YEARLY TOTAL            $32,196,912.42

GRAND TOTAL                               $140,707,584.82

*/

————

//

First complex example: GROUP BY..WITH ROLLUP is used to generate subtotal at the Quarter level, total at the Year level and grand total for all times AdventureWorks Cycle in business. While the output is easily understandable, the total query is on the complex side.

————

— Microsoft SQL grand total, total, subtotal generation with T-SQL GROUP BY ROLLUP

————

— MSSQL grouping function

USE AdventureWorks2008;

 

SELECT   TotalType = CASE

                       WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4)) = 1

                         THEN ‘GRAND TOTAL’

                       WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + ‘Q’ +

                            CONVERT(CHAR(1),(month(OrderDate) 1) / 3 + 1)) = 1  

                         THEN ‘TOTAL – YY’

                       WHEN GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + ‘Q’ +

                            CONVERT(CHAR(1),(month(OrderDate) 1) / 3 + 1) +

                            right(‘0’ + CONVERT(VARCHAR,month(OrderDate)),2)) = 1

                         THEN ‘SUBTOTAL – QQ’

                       ELSE

                     END,

         [Year] = ISNULL(left(CONVERT(CHAR(8),OrderDate,112),4),),

         [Quarter] = ISNULL(left(CONVERT(CHAR(8),OrderDate,112),4) + ‘Q’ +

                     CONVERT(CHAR(1),(month(OrderDate) 1) / 3 + 1),

                            ),

         [Month] = COALESCE(left(CONVERT(CHAR(8),OrderDate,112),4) + ‘Q’ +

                   CONVERT(CHAR(1),(month(OrderDate) 1) / 3 + 1) + right(‘0’ +

                   CONVERT(VARCHAR,month(OrderDate)),2),

                            ),

         Purchases = ‘$’ + CONVERT(VARCHAR,SUM(SubTotal),1)

FROM     Purchasing.PurchaseOrderHeader

GROUP BY left(CONVERT(CHAR(8),OrderDate,112),4),

         left(CONVERT(CHAR(8),OrderDate,112),4) + ‘Q’ +

              CONVERT(CHAR(1),(month(OrderDate) 1) / 3 + 1),

         left(CONVERT(CHAR(8),OrderDate,112),4) + ‘Q’ +

              CONVERT(CHAR(1),(month(OrderDate) 1) / 3 + 1) + right(‘0’ +

              CONVERT(VARCHAR,month(OrderDate)),2) WITH ROLLUP

ORDER BY GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4)),

         [Year],

         GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + ‘Q’ +

         CONVERT(CHAR(1),(month(OrderDate) 1) / 3 + 1)),

         [Quarter],

         GROUPING(left(CONVERT(CHAR(8),OrderDate,112),4) + ‘Q’ +

         CONVERT(CHAR(1),(month(OrderDate) 1) / 3 + 1) + right(‘0’ +

         CONVERT(VARCHAR,month(OrderDate)),2)),

         [Month]

/* Results 

 

TotalType         Year  Quarter     Month Purchases

                  2001  2001Q2      2001Q205    $103,895.82

SUBTOTAL – QQ     2001  2001Q2                  $103,895.82

TOTAL – YY        2001                          $103,895.82

                  2002  2002Q1      2002Q101    $299,239.98

                  2002  2002Q1      2002Q102    $700,406.63

                  2002  2002Q1      2002Q103    $328,572.46

SUBTOTAL – QQ     2002  2002Q1                  $1,328,219.07

                  2002  2002Q2      2002Q204    $646,975.84

                  2002  2002Q2      2002Q205    $298,745.12

                  2002  2002Q2      2002Q206    $106,491.00

SUBTOTAL – QQ     2002  2002Q2                  $1,052,211.96

                  2002  2002Q3      2002Q307    $655,405.08

                  2002  2002Q3      2002Q308    $116,119.65

                  2002  2002Q3      2002Q309    $499,186.65

SUBTOTAL – QQ     2002  2002Q3                  $1,270,711.38

                  2002  2002Q4      2002Q410    $186,121.65

                  2002  2002Q4      2002Q411    $388,868.98

SUBTOTAL – QQ     2002  2002Q4                  $574,990.63

TOTAL – YY        2002                          $4,226,133.03

                  2003  2003Q1      2003Q103    $131,485.79

SUBTOTAL – QQ     2003  2003Q1                  $131,485.79

                  2003  2003Q2      2003Q205    $641,097.40

                  2003  2003Q2      2003Q206    $924,127.80

SUBTOTAL – QQ     2003  2003Q2                  $1,565,225.19

                  2003  2003Q3      2003Q307    $105,014.51

                  2003  2003Q3      2003Q308    $98,304.85

                  2003  2003Q3      2003Q309    $5,100,678.73

SUBTOTAL – QQ     2003  2003Q3                  $5,303,998.10

                  2003  2003Q4      2003Q410    $3,449,392.92

                  2003  2003Q4      2003Q411    $1,775,204.15

                  2003  2003Q4      2003Q412    $3,424,287.10

SUBTOTAL – QQ     2003  2003Q4                  $8,648,884.17

TOTAL – YY        2003                          $15,649,593.25

                  2004  2004Q1      2004Q101    $4,266,022.62

                  2004  2004Q1      2004Q102    $4,137,584.18

                  2004  2004Q1      2004Q103    $4,704,085.56

SUBTOTAL – QQ     2004  2004Q1                  $13,107,692.37

                  2004  2004Q2      2004Q204    $5,172,365.20

                  2004  2004Q2      2004Q205    $5,729,967.42

                  2004  2004Q2      2004Q206    $5,522,963.61

SUBTOTAL – QQ     2004  2004Q2                  $16,425,296.24

                  2004  2004Q3      2004Q307    $6,710,223.01

                  2004  2004Q3      2004Q308    $6,824,989.46

                  2004  2004Q3      2004Q309    $743,151.66

SUBTOTAL – QQ     2004  2004Q3                  $14,278,364.13

                  2004  2004Q4      2004Q410    $1,020.00

SUBTOTAL – QQ     2004  2004Q4                  $1,020.00

TOTAL – YY        2004                          $43,812,372.74

GRAND TOTAL                                     $63,791,994.84

*/

————

Second complex example: summary query generates Subtotal, Total and Grand Total for monthly, yearly and all times sales. In data warehousing, the dates are referred to as date (or time) dimension with 3 levels.

————

— GRAND TOTAL (all times), TOTAL (year), SUBTOTAL (month) mssql select query

————

— Microsoft T-SQL derived table rpt generates the report – select from select

— The outer query does the final filtering and sorting

USE AdventureWorks;

 

SELECT *

FROM

(

  SELECT YY=COALESCE(CONVERT(varchar,YEAR(OrderDate)),),

         MM=COALESCE(LEFT(CONVERT(varchar,OrderDate,111),7),),

              ORDERS = COUNT(*),

              SALES = ‘$’+CONVERT(varchar,SUM(TotalDue),1),

          GRPMM = CASE WHEN

 GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 0

                       AND  GROUPING(YEAR(OrderDate)) = 1

                          THEN ‘SUBTOTAL’ ELSE END, 

          GRPYY= CASE WHEN GROUPING(YEAR(OrderDate)) = 0

AND  GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1

                         THEN ‘TOTAL’ ELSE END,

          GRPALL = CASE WHEN

                   GROUPING(LEFT(CONVERT(varchar,OrderDate,111),7)) = 1

                        AND GROUPING(YEAR(OrderDate)) = 1

                                  THEN ‘GRAND TOTAL’ ELSE END

 

  FROM Sales.SalesOrderHeader

  GROUP BY YEAR(OrderDate), LEFT(CONVERT(varchar,OrderDate,111),7)

WITH CUBE

) rpt

WHERE

       GRPMM != OR GRPYY != OR GRPALL !=

ORDER BY

            CASE WHEN GRPALL!= THEN 3

                 WHEN GRPYY != THEN 2

            ELSE 1 END,

            YY, MM

GO

/* Partial results

 

YY    MM          ORDERS      SALES             GRPMM       GRPYY GRPALL

      2004/04     2127        $4,268,473.54     SUBTOTAL         

      2004/05     2386        $5,813,557.45     SUBTOTAL         

      2004/06     2374        $6,004,155.77     SUBTOTAL         

      2004/07     976         $56,178.92        SUBTOTAL         

2001              1379        $12,693,250.63                TOTAL

2002              3692        $34,463,848.44                TOTAL

2003              12443       $47,171,489.55                TOTAL

2004              13950       $28,887,306.04                TOTAL

                  31464       $123,215,894.65                     GRAND TOTAL

*/

————

Third complex example: the MonthlyOrderSummary
stored procedure will generate Account Number Total (subtotal), Day Total (total)and Month Total
(grand total) columns.

————

— T-SQL grand total, total, subtotal stored procedure

————

— T-SQL create stored procedure

USE AdventureWorks2008;

GO

CREATE PROCEDURE MonthlyOrderSummary 

— ALTER PROCEDURE MonthlyOrderSummary

                @Year  INT,

                @Month INT

AS

  BEGIN

    SELECT   soh.AccountNumber,

             PO = isnull(soh.PurchaseOrderNumber,),

                  convert(CHAR(10),soh.OrderDate,111) AS ‘Order Date’,

             soh.TotalDue,

             CASE

               WHEN SalesOrderID =(

               SELECT   TOP 1 SalesOrderID

               FROM     Sales.SalesOrderHeader

               WHERE    convert(CHAR(10),OrderDate,111) =

                        convert(CHAR(10),soh.OrderDate,111)

                 ORDER BY SalesOrderID DESC) THEN (SELECT ‘$’ +

                       convert(VARCHAR,sum(TotalDue),1)

                       FROM   Sales.SalesOrderHeader

                       WHERE  SalesOrderID <= soh.SalesOrderID

                       AND convert(CHAR(11),OrderDate,111) =

                       convert(CHAR(10),soh.OrderDate,111))

               ELSE ‘ ‘

             END AS ‘Day Total’,

             CASE

               WHEN SalesOrderID =(

               SELECT   TOP 1 SalesOrderID

               FROM     Sales.SalesOrderHeader

               WHERE    year(OrderDate) = @Year

                   AND month(OrderDate) = @Month

                 ORDER BY OrderDate DESC) THEN (SELECT ‘$’ +

                 convert(VARCHAR,sum(TotalDue),1)

                      FROM   Sales.SalesOrderHeader

                      WHERE  year(OrderDate) = @Year

                         AND month(OrderDate) = @Month)

               ELSE ‘ ‘

             END AS ‘Month Total’

    FROM     Sales.SalesOrderHeader soh

    WHERE    year(OrderDate) = @Year

             AND month(OrderDate) = @Month

    ORDER BY SalesOrderID

  END

 

GO

 

— MSSQL execute stored procedure with 2 parameters

EXEC MonthlyOrderSummary   2003,  7

GO

 

/* Partial results – TotalDue is    SUBTOTAL

                     Day Total is   TOTAL

                     Month Total is GRAND TOTAL

                    

AccountNumber     PO    Order Date  TotalDue    Day Total   Month Total

10-4030-011069          2003/07/30  2563.589          

10-4030-011101          2003/07/30  2696.189          

10-4030-026306          2003/07/30  830.2307          

10-4030-024421          2003/07/30  2703.9903         

10-4030-022398          2003/07/30  596.689     $23,476.44 

10-4030-019435          2003/07/31  2715.3497              $4,681,520.64

*/

————


Calculating Running Totals, Subtotals and Grand Total Without a Cursor

If you have ever had the
need to show detailed data of individual transactions and also keep a running
total, subtotals, and grand total columns at the same time, but were not
exactly sure how to tackle the problem then this article might help. In this
article I will show you a few different techniques for calculating and summing
information on multiple rows without using a cursor. The techniques I will show
you will just use a basic SELECT statement. Of course, the calculations of
the running total, subtotals and grand total will be done using other SQL
clauses and functions like SUM and CASE.

Sample Data Used by Examples

Prior to showing you my
examples, I will first need to establish a set of test data, which all my
examples will use. My test data will consist of an “Orders” table with the
following format:

create table Orders
(OrderID int identity,
 OrderAmt Decimal(8,2),
 OrderDate SmallDatetime)

I’ve populated this test Orders
table with the following set of records:

OrderID     OrderAmt   OrderDate                                              
----------- ---------- --------------------
1           10.50      2003-10-11 08:00:00
2           11.50      2003-10-11 10:00:00
3           1.25       2003-10-11 12:00:00
4           100.57     2003-10-12 09:00:00
5           19.99      2003-10-12 11:00:00
6           47.14      2003-10-13 10:00:00
7           10.08      2003-10-13 12:00:00
8           7.50       2003-10-13 19:00:00
9           9.50       2003-10-13 21:00:00

All my examples will be
using this table to produce the running totals, sub totals, and grand total
reports shown below. Basically this table contains a number of different orders
that where created over time. Each order has an ID (OrderID) which uniquely
identifies each record, an order amount (OrderAmt) that holds a decimal amount
for the order, and a timestamp (OrderDate) that identifies when the order was placed.

Running Total On Each
Record

This first example will
display a simple method of calculating the running total of the OrderAmt. The
calculated running total will be displayed along with each record in the Orders
table. The “Running Total” column will be created with a simple SELECT
statement and a correlated sub query. The correlated sub query is the part of
the statement that does the heavy lifting to produce the running total.

select OrderId, OrderDate, O.OrderAmt
	,(select sum(OrderAmt) from Orders 
                          where OrderID <= O.OrderID)
	  'Running Total'
from Orders O

When I run this query
against my Orders table I get the following results:

OrderId     OrderDate            OrderAmt   Running Total                            
----------- -------------------- ---------- ------------- 
1           2003-10-11 08:00:00  10.50      10.50
2           2003-10-11 10:00:00  11.50      22.00
3           2003-10-11 12:00:00  1.25       23.25
4           2003-10-12 09:00:00  100.57     123.82
5           2003-10-12 11:00:00  19.99      143.81
6           2003-10-13 10:00:00  47.14      190.95
7           2003-10-13 12:00:00  10.08      201.03
8           2003-10-13 19:00:00  7.50       208.53
9           2003-10-13 21:00:00  9.50       218.03

As you can see, there is a
“Running Total” column that displays the running total along with the other
column information associated with each Orders table record. This running
total column is calculated, by summing up the OrderAmt for all Orders where the
OrderID is less than or equal to the OrderID of the current ID being displayed.

Running Total for Each
OrderDate

This example is similar to
the one above, but I will calculate a running total for each record, but only
if the OrderDate for the records are on the same date. Once the OrderDate is for
a different day, then a new running total will be started and accumulated for
the new day. Here is the code to accomplish this:

select O.OrderId,
    convert(char(10),O.OrderDate,101) as 'Order Date',
    O.OrderAmt, 
    (select sum(OrderAmt) from Orders 
                          where OrderID <= O.OrderID and 
                               convert(char(10),OrderDate,101)
                             = convert(char(10),O.OrderDate,101))
	                               'Running Total' 
from Orders O
  order by OrderID

Here are the results returned
from the query using my sample Orders Table:

OrderId     Order Date OrderAmt   Running Total                            
----------- ---------- ---------- ---------------
1           10/11/2003 10.50      10.50
2           10/11/2003 11.50      22.00
3           10/11/2003 1.25       23.25
4           10/12/2003 100.57     100.57
5           10/12/2003 19.99      120.56
6           10/13/2003 47.14      47.14
7           10/13/2003 10.08      57.22
8           10/13/2003 7.50       64.72
9           10/13/2003 9.50       74.22

Note that the “Running Total”
starts out with a value of 10.50, and then becomes 22.00, and finally becomes
23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But
when OrderID 4 is displayed the running total is reset, and the running total
starts over again. This is because OrderID 4 has a different date for its OrderDate,
then OrderID 1, 2, and 3. Calculating this running total for each unique date is
once again accomplished by using a correlated sub query, although an extra WHERE
condition is required, which identified that the OrderDate’s on different
records need to be the same day. This WHERE condition is accomplished by using
the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.

Running Totals With
Subtotals and Grand totals

In this example, I will
calculate a single sub totals for all Orders that were created on the same day and
a Grand Total for all Orders. This will be done using a CASE clause in the SELECT
statement. Here is my example.

select O.OrderID,convert(char(10),O.OrderDate,101) 'Order Date',O.OrderAmt, 
       case when OrderID = (select top 1 OrderId from Orders 
                           where convert(char(10),OrderDate,101)
                              = convert(char(10),O.OrderDate,101)
                          order by OrderID desc)
            then (select cast(sum(OrderAmt) as char(10)) 
                     from Orders
                     where OrderID <= O.OrderID
                        and convert(char(10),OrderDate,101)
                           = convert(char(10),O.OrderDate,101))
            else ' ' end as 'Sub Total',
       case when OrderID = (select top 1 OrderId from Orders 
                           order by OrderDate desc)
            then (select cast(sum(OrderAmt) as char(10)) 
                      from Orders) 
             else ' ' end as 'Grand Total'
from Orders O
  order by OrderID

Output from the SELECT
statement looks like this:

OrderID     Order Date OrderAmt   Sub Total  Grand Total 
----------- ---------- ---------- ---------- ----------- 
1           10/11/2003 10.50                           
2           10/11/2003 11.50                           
3           10/11/2003 1.25       23.25                
4           10/12/2003 100.57                          
5           10/12/2003 19.99      120.56               
6           10/13/2003 47.14                           
7           10/13/2003 10.08                           
8           10/13/2003 7.50                            
9           10/13/2003 9.50       74.22      218.03

In this example the first
CASE statement controls the printing of the “Sub Total’ column. As you can
see, the sub total is printed only on the last order of the day, which is
determined by using a correlated sub query. The second CASE statement prints
the “Grand Total”, which is only printed along with the very last order. Each
of these CASE statements uses the TOP clause to determine which OrderID is the
correct order for which to print out the “Grand Total”.

Conclusion

Hopefully these examples
will help you understand different methods that can be used to calculate
running totals, sub totals, and a grand total. As you can see you don’t need
to use a cursor to calculate these different totals. With the creative use of
correlated sub queries and other SELECT clauses like CASE you can easily create
all these different totals. Next time you need to calculate totals consider
using one of these non-cursor based solutions.

»

Reference  :   Gregory A. Larsen

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If stored procedure was created but never modified afterwards modified date and create date for that stored procedure are same.

SELECT name

FROM sys.objects

WHERE type = 'P'

AND DATEDIFF(D,modify_date, GETDATE()) < 7

----Change 7 to any other day value

Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT name

FROM sys.objects

WHERE type = 'P'

AND DATEDIFF(D,create_date, GETDATE()) < 7

----Change 7 to any other day value.

Date condition in above script can be adjusted to retrieve required data.

Reference : Pinal Dave (http://blog.SQLAuthority.com)