mgm6608美高梅app下载-mgm集团美高梅登录

mgm6608美高梅app下载以独家报道、社区、博客和专题策划等方式引领产业潮流,mgm集团美高梅登录为足球爱好者以及体育爱好者提供足球上网,让你无后顾之忧!,拥有多位香港乐坛红星。

4. datepart 返回代表指定日期的指定日期部分的整

日期:2020-04-16编辑作者:mgm集团美高梅登录

表结构如下: qtydate ---------------------------------------------- 132005/01/17 152005/01/19 32005/01/25 1052005/01/27 12005/01/31 3522005/02/03 122005/02/04 2552005/02/07 62005/02/18 12005/02/19 282005/02/21 12005/02/22 3942005/02/23 3592005/02/24 3132005/02/25 3252005/02/26 5442005/02/27 682005/02/28 22005/03/01 求一個SQL寫法,將每個月的數量求和, 如: qrydate 1372005/01 ??2005/02 ??2005/03 SQL语句如下: selectsum(qty),datename(year,date)asyear,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]mgm集团美高梅登录,) = '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]mgm6608美高梅app下载,) 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 参数可以是下列的值:

mgm6608美高梅app下载 1

四、实例

1、截取年份:datepart(yy,'2017-1-1') 返回:2017

2、截取月份:datepart(mm,'2017-1-1') 返回:1

五、datepart函数返回的是整型数值,如果需要返回字符型,那么使用datename()函数,用法与datepart相同,只是返回数据类型不同。

 

常见错误

--选择列表中的列 'dbo.v_yjdatealljg.cjrq' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
--把所有按组排序的依据都加上 yjxzqid, cjdd, ncpid, YEAR([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……52), 这里星期一是星期的第一天 
%% 一个文字“%”。

 

 

 

本文只是记录在项目中用到的统计的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(10),getdate(),120)

,当前时间=convert(varchar(8),getdate(),114)

 

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

 

select 本年第多少周=datename(week,getdate())

,今天是周几=datename(weekday,getdate())

 

 

 

函数 参数/功能 

GetDate( ) --返回系统目前的日期与时间 

DateDiff (interval,date1,date2) --以interval 指定的方式,返回date2 与date1两个日期之间的差值 date2-date1 

DateAdd (interval,number,date) --以interval指定的方式,加上number之后的日期 

本文由mgm6608美高梅app下载发布于mgm集团美高梅登录,转载请注明出处:4. datepart 返回代表指定日期的指定日期部分的整

关键词:

msver 3. 查看数据库启动的参数 sp

查看数据库的版本 select @@version 2. 查看数据库所在机器操作系统参数exec master..xp_msver 3. 查看数据库启动的参数 sp_co...

详细>>

导出的文件名(数据库默认编码是latin1),SELECT语句

SQL语句的基本语法 尹红写的,实在忍不主贴在这里做为查看资料.因为原文是在word里面写的,段落有点乱. 一.Select语句的...

详细>>

口令加密都是单向的

mgm6608美高梅app下载 ,如果对MSSQL的用户信息有兴趣的,可能会发现master.dbo.sysxlogins里面存放着用户的口令,可是呢,...

详细>>

2、然后点上面菜单中的工具--选择数据库维护计

通过企业管理器设置数据库的定期自动备份计划。1、打开企业管理器,双击打开你的服务器2、然后点上面菜单中的工...

详细>>