按月统计数据,年统计数据的方法

表布局如下: qtydate ———————————————-
13二〇〇六/01/17 152007/01/19 3二零零六/01/35 1052007/052%7 1二零零五/0四分之一1
3522006/02/03 12二〇〇五/02/04 255二〇〇五/02/07 6二零零六/02/18 1二零零七/02/一九三零二〇〇五/02/21 1贰零零伍/02/22 3942007/02/23 3592006/02/24 313二〇〇六/02/25
3252006/02/26 544二〇〇五/02/27 68二〇〇五/02/28 2二〇〇七/03/01
求一個SQL寫法,將每個月的數量求和, 如: qrydate 137二零零五/01 ??二零零六/02
??二〇〇五/03 SQL语句如下:
selectsum(qty卡塔尔国,datename(year,dateState of Qatarasyear,datename(month,date卡塔尔asmonth
fromtable groupbyyear,month

转载源:

1. 当前系统日期、时间

SQL遵照日、周、月、季度、年总计数据的法门

方式一:

–按日 
select sum(consume),day([date]) from consume_record where
year([date]) = ‘2006’ group by day([date])

–按周quarter 
select sum(consume),datename(week,[date]) from consume_record where
year([date]) = ‘2006’ group by datename(week,[date])

–按月 
select sum(consume),month([date]) from consume_record where
year([date]) = ‘2006’ group by month([date])

–按季 
select sum(consume),datename(quarter,[date]) from consume_record
where year([date]) = ‘2006’ group by datename(quarter,[date]) 
 

–按年
select sum(consume),year([date]) from consume_record where  group by
year([date])

方式二:

sqlserver 截取日期年份和月份选取datepart函数,函数使用格局如下:

一、函数功用:DATEPART(卡塔尔国函数用于再次来到日期/时间的独自部分,举例年、月、日、小时、分钟等等。

二、语法:DATEPART(datepart,date)

三、参数表达:date 参数是合法的日期表达式。datepart 参数能够是下列的值:

图片 1

四、实例

1、截取年份:datepart(yy,’2017-1-1’卡塔尔(قطر‎ 重临:2017

2、截取月份:datepart(mm,’2017-1-1’卡塔尔国 重回:1

五、datepart函数重返的是整型数值,就算急需回到字符型,那么使用datename(State of Qatar函数,用法与datepart相似,只是重临数据类型差异。

 

广泛错误

–选拔列表中的列 ‘dbo.v_yjdatealljg.cjrq’
无效,因为该列未有富含在聚合函数或 GROUP BY 子句中。
把具有按组排序的基于都加多 yjxzqid,
cjdd, ncpid, YEA传祺([cjrq]), month([cjrq])

–去掉农成品名称 ncpmc
SELECT yjxzqid, cjdd, ncpid, YEAR([cjrq]) as [year], month([cjrq])
as [month], avg( ttjg)as ttjg, avg(pfjg)as pfjg, avg(lsjg)as lsjg
FROM dbo.v_yjdatealljg group by yjxzqid,
cjdd, ncpid, YEAR([cjrq]), month([cjrq])

 

DATE_FORMAT

1
2
3
select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;

DATE_FORMAT(date,format) 
基于format字符串格式化date值。下列修饰符能够被用在format字符串中: 
%M 月名字(January……December) 
%W 星期名字(Sunday……Saturday卡塔尔(قطر‎ 
%D 有保加莱切斯特语前缀的月度的日子(1st, 2nd, 3rd, 等等。) 
%Y 年, 数字, 4 位 
%y 年, 数字, 2 位 
%a 缩写的星期名字(Sun……Sat卡塔尔国 
%d 月份中的天数, 数字(00……31卡塔尔 
%e 月份中的天数, 数字(0……31卡塔尔(قطر‎ 
%m 月, 数字(01……12) 
%c 月, 数字(1……12) 
%b 缩写的月度名字(Jan……Dec卡塔尔国 
%j 一年中的天数(001……366卡塔尔(قطر‎ 
%H 小时(00……23) 
%k 小时(0……23) 
%h 小时(01……12) 
%I 小时(01……12) 
%l 小时(1……12) 
%i 分钟, 数字(00……59) 
%r 时间,12 小时(hh:mm:ss [AP]M) 
%T 时间,24 小时(hh:mm:ss) 
%S 秒(00……59) 
%s 秒(00……59) 
%p AM或PM 
%w 贰个星期中的天数(0=Sunday ……6=Saturday ) 
%U 星期(0……52卡塔尔(قطر‎, 这里周末是星期的首后天 
%u 星期(0……52State of Qatar, 这里周二是星期的首后天 
%% 多个文字“%”。

 

 

 

正文只是记录在类型中用到的总括的SQL语句,记一笔避防忘了

 

 

 

 

 

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
/// <summary>
   /// 获取统计数据
   /// </summary>
   /// <param name="CKEY">店面ckey</param>
   /// <param name="type">统计类型(日、周、月、年)</param>
   /// <returns></returns>
   [WebMethod(true)]
   public static string GetData3(string CKEY, string type)
   {
     StringBuilder strSql = new StringBuilder();
      
     #region SQL语句
 
     if (type == "0")
     {
       #region 日
       strSql.AppendFormat(" WITH  WeekDate ");
       strSql.AppendFormat("     AS ( SELECT  DATEADD(d, -DAY(GETDATE()) + 1, GETDATE()) AS riqi ");
       strSql.AppendFormat("       UNION ALL ");
       strSql.AppendFormat("       SELECT  riqi + 1 FROM   WeekDate ");
       strSql.AppendFormat("       WHERE  riqi + 1 <= ( SELECT  DATEADD(d, -DAY(GETDATE()), DATEADD(m, 1, GETDATE())) ) ");
       strSql.AppendFormat("      ) ");
       strSql.AppendFormat("  SELECT CONVERT(CHAR(8), a.riqi, 112) AS 日 ,DAY (CONVERT(CHAR(8), a.riqi, 112)) AS DDay, ");
       strSql.AppendFormat("      ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
       strSql.AppendFormat("      CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("        THEN NULL ");
       strSql.AppendFormat("        WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("        THEN ISNULL(tbB.日成交量, 0) ");
       strSql.AppendFormat("      END AS 日成交数量 , ");
       strSql.AppendFormat("      tbB.日实收金额 , ");
       strSql.AppendFormat("      CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("        THEN NULL ");
       strSql.AppendFormat("        WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("        THEN ISNULL(tbB.日实收金额, 0) ");
       strSql.AppendFormat("      END AS 日实收金额2 ");
       strSql.AppendFormat("  FROM  WeekDate a ");
       strSql.AppendFormat("      LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
       strSql.AppendFormat("                 FROM   dbo.CustomerBase base ");
       strSql.AppendFormat("                 WHERE   CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                      AND " + impomo.TotalConsumptionMon + " > 0 ");
       strSql.AppendFormat("                      AND TargetDate = cus.TargetDate ");
       strSql.AppendFormat("                ) 日成交量 , ");
       strSql.AppendFormat("                ISNULL(( SELECT SUM(Total) ");
       strSql.AppendFormat("                    FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                         FROM   PaymentContent AS pay ");
       strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                              AND pay.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                         UNION ALL ");
       strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
       strSql.AppendFormat("                         FROM   dbo.CardRecharge8 AS recharge ");
       strSql.AppendFormat("                         WHERE   RechargDate = cus.TargetDate ");
       strSql.AppendFormat("                              AND recharge.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                         UNION ALL ");
       strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                         FROM   dbo.PaymentSwimming AS payswim ");
       strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                              AND payswim.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                         UNION ALL ");
       strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
       strSql.AppendFormat("                         FROM   WarePaymentContent AS ware ");
       strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                              AND ware.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                        ) B ");
       strSql.AppendFormat("                   ), 0) AS 日实收金额 , ");
       strSql.AppendFormat("                TargetDate 日 ");
       strSql.AppendFormat("            FROM  dbo.CustomerBase cus ");
       strSql.AppendFormat("            WHERE  YEAR(TargetDate) = YEAR(GETDATE()) ");
       strSql.AppendFormat("                AND MONTH(TargetDate) = MONTH(GETDATE()) ");
       strSql.AppendFormat("            GROUP BY TargetDate ");
       strSql.AppendFormat("           ) AS tbB ON CONVERT(CHAR(8), a.riqi, 112) = tbB.日 ");
       #endregion
     }
     else if (type == "1")
     {
       #region 周
       strSql.AppendFormat(" WITH  WeekDate ");
       strSql.AppendFormat("       AS ( SELECT  DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) AS riqi ");
       strSql.AppendFormat("         UNION ALL ");
       strSql.AppendFormat("         SELECT  riqi + 1 FROM   WeekDate ");
       strSql.AppendFormat("         WHERE  riqi + 1 <= ( SELECT  DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6) ) ");
       strSql.AppendFormat("        ) ");
       strSql.AppendFormat("    SELECT CONVERT(CHAR(8), a.riqi, 112) AS 日 , ");
       strSql.AppendFormat("        DATENAME(weekday,CONVERT(CHAR(8), a.riqi, 112)) DDay, ");
       strSql.AppendFormat("        ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
       strSql.AppendFormat("        CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("          THEN NULL ");
       strSql.AppendFormat("          WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("          THEN ISNULL(tbB.日成交量, 0) ");
       strSql.AppendFormat("        END AS 日成交数量 , ");
       strSql.AppendFormat("        tbB.日实收金额 , ");
       strSql.AppendFormat("        CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("          THEN NULL ");
       strSql.AppendFormat("          WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("          THEN ISNULL(tbB.日实收金额, 0) ");
       strSql.AppendFormat("        END AS 日实收金额2 ");
       strSql.AppendFormat("    FROM  WeekDate a ");
       strSql.AppendFormat("        LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
       strSql.AppendFormat("                   FROM   dbo.CustomerBase base ");
       strSql.AppendFormat("                   WHERE   CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                        AND " + impomo.TotalConsumptionMon + " > 0 ");
       strSql.AppendFormat("                        AND TargetDate = cus.TargetDate ");
       strSql.AppendFormat("                  ) 日成交量 , ");
       strSql.AppendFormat("                  ISNULL(( SELECT SUM(Total) ");
       strSql.AppendFormat("                      FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                           FROM   PaymentContent AS pay ");
       strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                                AND pay.CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                           UNION ALL ");
       strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
       strSql.AppendFormat("                           FROM   dbo.CardRecharge8 AS recharge ");
       strSql.AppendFormat("                           WHERE   RechargDate = cus.TargetDate ");
       strSql.AppendFormat("                                AND recharge.CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                           UNION ALL ");
       strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                           FROM   dbo.PaymentSwimming AS payswim ");
       strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                                AND payswim.CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                           UNION ALL ");
       strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
       strSql.AppendFormat("                           FROM   WarePaymentContent AS ware ");
       strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                                AND ware.CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                          ) B ");
       strSql.AppendFormat("                     ), 0) AS 日实收金额 , ");
       strSql.AppendFormat("                  TargetDate 日 ");
       strSql.AppendFormat("              FROM  dbo.CustomerBase cus ");
       strSql.AppendFormat("              WHERE  DATEPART(wk, TargetDate) = DATEPART(wk, GETDATE()) ");
       strSql.AppendFormat("                  AND DATEPART(yy, TargetDate) = DATEPART(yy, GETDATE()) ");
       strSql.AppendFormat("              GROUP BY TargetDate ");
       strSql.AppendFormat("             ) AS tbB ON CONVERT(CHAR(8), a.riqi, 112) = tbB.日 ");
       #endregion
     }
     else if (type == "2")
     {
       #region 月
 
       strSql.AppendFormat("SELECT YearMonth.月 , ");
       strSql.AppendFormat("    tb.月成交量 , ");
       strSql.AppendFormat("    CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
       strSql.AppendFormat("      WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月成交量, 0) ");
       strSql.AppendFormat("    END AS 月成交数量 , ");
       strSql.AppendFormat("    tb.月实收总金额 , ");
       strSql.AppendFormat("    CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
       strSql.AppendFormat("      WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月实收总金额, 0) ");
       strSql.AppendFormat("    END AS 月实收总金额2 ");
       strSql.AppendFormat(" FROM   ( SELECT 1 AS 月 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ");
       strSql.AppendFormat("       UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 ");
       strSql.AppendFormat("      ) AS YearMonth ");
       strSql.AppendFormat("    LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
       strSql.AppendFormat("               FROM   dbo.CustomerBase base ");
       strSql.AppendFormat("               WHERE   CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                    AND " + impomo.TotalConsumptionMon + " > 0 ");
       strSql.AppendFormat("                    AND MONTH(TargetDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("              ) 月成交量 , ");
       strSql.AppendFormat("              ISNULL(( SELECT SUM(Total) ");
       strSql.AppendFormat("                  FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                       FROM   PaymentContent AS pay ");
       strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("                            AND pay.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                       UNION ALL ");
       strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
       strSql.AppendFormat("                       FROM   dbo.CardRecharge8 AS recharge ");
       strSql.AppendFormat("                       WHERE   MONTH(RechargDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("                            AND recharge.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                       UNION ALL ");
       strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                       FROM   dbo.PaymentSwimming AS payswim ");
       strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("                            AND payswim.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                       UNION ALL ");
       strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
       strSql.AppendFormat("                       FROM   WarePaymentContent AS ware ");
       strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("                            AND ware.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                      ) B ");
       strSql.AppendFormat("                 ), 0) AS 月实收总金额 , ");
       strSql.AppendFormat("              MONTH(TargetDate) 月 ");
       strSql.AppendFormat("          FROM  dbo.CustomerBase cus ");
       strSql.AppendFormat("          WHERE  YEAR(TargetDate) = YEAR(GETDATE()) ");
       strSql.AppendFormat("          GROUP BY MONTH(cus.TargetDate) ");
       strSql.AppendFormat("         ) AS tb ON YearMonth.月 = tb.月 ");
       #endregion
     }
     else if (type == "3")
     {
       #region 年
       strSql.AppendFormat("SELECT ( SELECT  COUNT(1) ");
       strSql.AppendFormat("       FROM   dbo.CustomerBase base ");
       strSql.AppendFormat("       WHERE   CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("            AND " + impomo.TotalConsumptionMon + " > 0 ");
       strSql.AppendFormat("            AND YEAR(TargetDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("      ) 年成交量 , ");
       strSql.AppendFormat("      CONVERT(NVARCHAR(20),CONVERT(DECIMAL(18,2),ISNULL(( SELECT SUM(Total) ");
       strSql.AppendFormat("          FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("               FROM   PaymentContent AS pay ");
       strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("                    AND pay.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("               UNION ALL ");
       strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
       strSql.AppendFormat("               FROM   dbo.CardRecharge8 AS recharge ");
       strSql.AppendFormat("               WHERE   YEAR(RechargDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("                    AND recharge.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("               UNION ALL ");
       strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("               FROM   dbo.PaymentSwimming AS payswim ");
       strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("                    AND payswim.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("               UNION ALL ");
       strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
       strSql.AppendFormat("               FROM   WarePaymentContent AS ware ");
       strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("                    AND ware.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("              ) B ");
       strSql.AppendFormat("         ), 0))) AS 年实收总金额 , ");
       strSql.AppendFormat("      YEAR(TargetDate) 年 ");
       strSql.AppendFormat("  FROM  dbo.CustomerBase cus ");
       strSql.AppendFormat("  GROUP BY YEAR(TargetDate) ");
       #endregion
     }
 
     #endregion
 
     DataTable table = DBHelper.GetDateTable(strSql.ToString());
     string rs = Newtonsoft.Json.JsonConvert.SerializeObject(table);

select getdate() 

2. dateadd 在向钦赐日期加上一段时间的幼功上,重临新的 datetime 值

比方:向日期加上2天

select dateadd(day,2,’2004-10-15′) –返回:2004-10-17 00:00:00.000

 

3. datediff 再次回到跨七个钦命日期的日期和岁月界限数。

select datediff(day,’2004-09-01′,’2004-09-18′) –返回:17

 

4. datepart 重回代表钦赐日期的钦点日期部分的整数。

SELECT DATEPART(month, ‘2004-10-15’) –返回 10

 

5. datename 再次回到代表内定日期的钦点日期部分的字符串

SELECT datename(weekday, ‘2004-10-15’) –返回:星期五

 

6. day(卡塔尔, month(卡塔尔,year(卡塔尔国 –能够与datepart对照一下

 

select 当前些天期=convert(varchar(10State of Qatar,getdate(卡塔尔,120卡塔尔(قطر‎

,当前光阴=convert(varchar(8卡塔尔,getdate(卡塔尔国,114卡塔尔(قطر‎

 

7. select datename(dw,’2004-10-15′)

 

select 后年第多少周=datename(week,getdate(卡塔尔国卡塔尔(قطر‎

,几日前是周几=datename(weekday,getdate(卡塔尔State of Qatar

 

 

 

函数 参数/功能 

GetDate( 卡塔尔 –重回系统当下的日子与时光 

DateDiff (interval,date1,date2State of Qatar –以interval 内定的点子,重回date2 与date1三个日子之间的差值 date2-date1 

DateAdd (interval,number,date卡塔尔 –以interval钦定的艺术,加上number之后的日期 

DatePart (interval,dateState of Qatar —再次来到日期date中,interval钦赐部分所对应的整数值 

DateName (interval,date卡塔尔 –再次来到日期date中,interval内定部分所对应的字符串名称 

 

参数 interval的设定值如下:

 

值 缩 写(Sql Server) Access 和 ASP 说明 

Year Yy yyyy 年 1753 ~ 9999 

Quarter Qq q 季 1 ~ 4 

Month Mm m 月1 ~ 12 

Day of year Dy y 一年的日数,一年中的第几日 1-366 

Day Dd d 日,1-31 

Weekday Dw w 十三日的日数,二十二日中的第几日 1-7 

Week Wk ww 周,一年中的第几周 0 ~ 51 

Hour Hh h 时0 ~ 23 

Minute Mi n 分钟0 ~ 59 

Second Ss s 秒 0 ~ 59 

Millisecond Ms – 毫秒 0 ~ 999 

 

access 和 asp 中用date(卡塔尔(قطر‎和now(卡塔尔获得系统日期时间;在那之中DateDiff,DateAdd,DatePart也同是能用来Access和asp中,这么些函数的用法也就好像

 

举例:

1.GetDate() 用于sql server :select GetDate()

 

2.DateDiff(‘s’,’二零零七-07-20′,’二〇〇六-7-25 22:56:32’卡塔尔国重返值为 514592 秒

DateDiff(‘d’,’二零零五-07-20′,’二〇〇七-7-25 22:56:32’State of Qatar再次来到值为 5 天

 

3.DatePart(‘w’,’二零零五-7-25 22:56:32’State of Qatar再次回到值为 2 即星期二(礼拜六为1,星期天为7卡塔尔

DatePart(‘d’,’二零零六-7-25 22:56:32’卡塔尔国再次回到值为 25即25号

DatePart(‘y’,’二〇〇六-7-25 22:56:32’卡塔尔国重临值为 206即今年中第206天

DatePart(‘yyyy’,’2007-7-25 22:56:32’卡塔尔国重回值为 二〇〇五即二〇〇五年 

 

SQL Server DATEPART(State of Qatar 函数重临 SQLServer datetime 字段的一有些。 

 

SQL Server DATEPART(卡塔尔国 函数的语法是: 

DATEPART(portion, datetime)

 

其间 datetime 是 SQLServer datetime 字段和局部的称呼是下列之一: Ms for Milliseconds

Yy for Year

Qq for Quarter of the Year

Mm for Month

Dy for the Day of the Year

Dd for Day of the Month

Wk for Week

Dw for the Day of the Week

Hh for Hour

Mi for Minute

Ss for Second

 

 

–1.编写制定函数,实现遵照’年月日,星期几,上午午后夜晚’输出时间消息(2008年四月十七日星期四凌晨卡塔尔(قطر‎

select datename(yy,getdate()) + ‘年’ +

       datename(mm,getdate()) + ‘月’ + 

       datename(dd,getdate()) + ‘日’ +

       datename(weekday,getdate()) +

     
 case when datename(hh,getdate()) < 12 then ‘上午’ else ‘下午’ end                                                                                                                            

–2.编纂函数,依照输入时间。输出该天是该年的第几天

select datepart(dy,getdate())

–3.求出放肆输出字符‘a-z

select char(97+abs(checksum(newid()))%26)

select char(97+rand()*26)

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注