1.一个月第一天的
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

2.本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

3.一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

4.季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

5.当天的半夜
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

6.上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

7.去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

8.本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

9.本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

10.本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

1.用一条语句得出某日期所在月份的最大天数？

SELECT DAY(DATEADD(dd, -DAY('2004-02-13'), DATEADD(mm, 1, '2004-02-13'))) AS 'Day Number'

2.少记录变成多条记录问题

有表tbl
日期       收入    支出
2004-02-11 00:00:00 60 45
2004-03-01 00:00:00 60 45
2004-03-02 00:00:00 40 50
2004-03-05 00:00:00 50 40

/*
测试数据：
Create Table tbl([日期] smalldatetime,[收入] int ,[支出] int)

Insert Into tbl
SELECT '2004-02-11', 60, 45
union SELECT '2004-03-01',60, 45
union SELECT '2004-03-02',40, 50
union SELECT '2004-03-05',50, 40
*/

要得到的结果：
日期       收入    支出     余额
2004-02-01 00:00:00 NULL NULL NULL
2004-02-02 00:00:00 NULL NULL NULL
2004-02-03 00:00:00 NULL NULL NULL
2004-02-04 00:00:00 NULL NULL NULL
2004-02-05 00:00:00 NULL NULL NULL
2004-02-06 00:00:00 NULL NULL NULL
2004-02-07 00:00:00 NULL NULL NULL
2004-02-08 00:00:00 NULL NULL NULL
2004-02-09 00:00:00 NULL NULL NULL
2004-02-10 00:00:00 NULL NULL NULL
2004-02-11 00:00:00 60 45 15
2004-02-12 00:00:00 NULL NULL 15
2004-02-13 00:00:00 NULL NULL 15
2004-02-14 00:00:00 NULL NULL 15
2004-02-15 00:00:00 NULL NULL 15
2004-02-16 00:00:00 NULL NULL 15
2004-02-17 00:00:00 NULL NULL 15
2004-02-18 00:00:00 NULL NULL 15
2004-02-19 00:00:00 NULL NULL 15
2004-02-20 00:00:00 NULL NULL 15
2004-02-21 00:00:00 NULL NULL 15
2004-02-22 00:00:00 NULL NULL 15
2004-02-23 00:00:00 NULL NULL 15
2004-02-24 00:00:00 NULL NULL 15
2004-02-25 00:00:00 NULL NULL 15
2004-02-26 00:00:00 NULL NULL 15
2004-02-27 00:00:00 NULL NULL 15
2004-02-28 00:00:00 NULL NULL 15
2004-02-29 00:00:00 NULL NULL 15
2004-03-01 00:00:00 60 45 30
2004-03-02 00:00:00 40 50 20
2004-03-03 00:00:00 NULL NULL 20
2004-03-04 00:00:00 NULL NULL 20
2004-03-05 00:00:00 50 40 30
2004-03-06 00:00:00 NULL NULL 30
2004-03-07 00:00:00 NULL NULL 30
2004-03-08 00:00:00 NULL NULL 30
2004-03-09 00:00:00 NULL NULL 30
2004-03-10 00:00:00 NULL NULL 30
2004-03-11 00:00:00 NULL NULL 30
2004-03-12 00:00:00 NULL NULL 30
2004-03-13 00:00:00 NULL NULL 30
2004-03-14 00:00:00 NULL NULL 30
2004-03-15 00:00:00 NULL NULL 30
2004-03-16 00:00:00 NULL NULL 30
2004-03-17 00:00:00 NULL NULL 30
2004-03-18 00:00:00 NULL NULL 30
2004-03-19 00:00:00 NULL NULL 30
2004-03-20 00:00:00 NULL NULL 30
2004-03-21 00:00:00 NULL NULL 30
2004-03-22 00:00:00 NULL NULL 30
2004-03-23 00:00:00 NULL NULL 30
2004-03-24 00:00:00 NULL NULL 30
2004-03-25 00:00:00 NULL NULL 30
2004-03-26 00:00:00 NULL NULL 30
2004-03-27 00:00:00 NULL NULL 30
2004-03-28 00:00:00 NULL NULL 30
2004-03-29 00:00:00 NULL NULL 30
2004-03-30 00:00:00 NULL NULL 30
2004-03-31 00:00:00 NULL NULL 30

答案：
SELECT Y.[日期], tbl.[收入], tbl.[支出], (
 SELECT SUM(ISNULL(tbl.[收入], 0)-ISNULL(tbl.[支出], 0)) FROM tbl WHERE [日期]<=Y.[日期]) AS [余额]
 FROM tbl RIGHT JOIN (
  SELECT DATEADD(dd, N.i, DATEADD(dd, 1-DAY(m.MinDay), m.MinDay)) AS [日期]
  FROM (
   SELECT 0 AS i
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9
   UNION ALL SELECT 10
   UNION ALL SELECT 11
   UNION ALL SELECT 12
   UNION ALL SELECT 13
   UNION ALL SELECT 14
   UNION ALL SELECT 15
   UNION ALL SELECT 16
   UNION ALL SELECT 17
   UNION ALL SELECT 18
   UNION ALL SELECT 19
   UNION ALL SELECT 20
   UNION ALL SELECT 21
   UNION ALL SELECT 22
   UNION ALL SELECT 23
   UNION ALL SELECT 24
   UNION ALL SELECT 25
   UNION ALL SELECT 26
   UNION ALL SELECT 27
   UNION ALL SELECT 28
   UNION ALL SELECT 29
   UNION ALL SELECT 30
   UNION ALL SELECT 31
  ) N,
  (
   SELECT MIN(日期) AS MinDay
   FROM tbl
   GROUP BY DATEDIFF(month, 0, 日期)
  ) M
  WHERE DATEDIFF(mm, DATEADD(dd, N.i, DATEADD(dd, 1-DAY(m.MinDay), m.MinDay)), M.MinDay)=0) AS Y
 ON tbl.[日期]=Y.日期