對時間函數(shù)、字符串格式的一些總結(jié)

lanyunleng 2012-09-19

展開全文

一、年月周統(tǒng)計DEMO

　　公司正式數(shù)據(jù)庫比較復(fù)雜，加上表內(nèi)數(shù)據(jù)不便輕易修改，所以在項目之前，我新建了個數(shù)據(jù)表用來測試代碼的可行性，正式項目是體力活，但知識點

就這些。

　　圖是一個存放康城小區(qū)、紅河小區(qū)2012年1月份的水電費表(項目中，這是一多張表組成的臨時表，字段要多一些，但表結(jié)構(gòu)一致)。

　　項目的要求是：按照年、月、周、日分別對其進行統(tǒng)計，按日統(tǒng)計比較簡單，不再累述。

　　為了方便測試，我在表內(nèi)添加兩條新數(shù)據(jù)。

1 insert into EnergySettings(PointID,ValueTime,BuildName,FieldName,Value) values('A','2011-12-31','康城小區(qū)','電費','100')
2 
3 insert into EnergySettings(PointID,ValueTime,BuildName,FieldName,Value) values('A','2012-2-1','紅河小區(qū)','水費','100')

　　此時表內(nèi)，含有2011年12月一條數(shù)據(jù)，2012年1月31*4條數(shù)據(jù)，和2012年2月一條數(shù)據(jù)。

1按年統(tǒng)計(用到YEAR()函數(shù))

1 select PointID,YEAR(ValueTime)as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue from EnergySettings
2 group by PointID,YEAR(ValueTime),BuildName,FieldName

2按月統(tǒng)計(用到MONTH()函數(shù))

1 select PointID,YEAR(ValueTime)*100+MONTH(ValueTime)as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
2 from EnergySettings
3 group by PointID,YEAR(ValueTime)*100+MONTH(ValueTime),BuildName,FieldName

3按周統(tǒng)計(用到DATEPART()函數(shù))

　　按周統(tǒng)計需要注意兩個細節(jié)：

　　① 中西方觀念不同，西方把周日作為一周的開始，我們習(xí)慣把周一作為一周的第一天。在SQL Server中，默認周日為一周的開始，所以我們需要根

據(jù)業(yè)務(wù)需求(set datefirst )來對一周的第一天是星期一還是星期日進行設(shè)置。

　　② 本年的第一個周和去年的最后一周是分開的，比如， 2011-12-31是星期六，2012-01-01是星期日，如果我們設(shè)置周一為一周的第一天，那

么2011-12-31是2011年第53周，2012-01-01是2012年第一周，這兩周分開，也就是說2011年第53周有6天，2012年第一周只有1天。并不能籠

統(tǒng)地說2011年第53周就是2012年第一周。

　　以康城小區(qū)的電費為例：

　　默認下，周日為周起始：

1 select PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime) as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
2  from EnergySettings where BuildName='康城小區(qū)'and FieldName='電費'
3 group by PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime),BuildName,FieldName

　　我們可以設(shè)置周一為一周開始：

set datefirst 1   --設(shè)置周一為一周的開始/datefirst 7=周日為一周開始
select PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime) as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
from EnergySettings where BuildName='康城小區(qū)'and FieldName='電費'group by PointID,YEAR(ValueTime)*100+DATEPART(w
eek,ValueTime),BuildName,FieldName

　　項目界面展示的時候，我們可以用下面GetNewWeekDays()方法把 201202轉(zhuǎn)化為20120102～20120108這種格式(此方法在單獨一年可用，

連續(xù)多年時需稍作修改，原因就是上面我們提到的細節(jié)②)。

 1 /// <summary>
 2 /// 根據(jù)傳值得周一到周日日期 
 3 /// </summary>
 4 private string GetNewWeekDays(string yearWeekindex)
 5  {
 6  　　DateTime yearFirstDay = Convert.ToDateTime(yearWeekindex.Substring(0, 4) + "-01-01");//獲得本年第一天的日期
 7 　　 if (yearFirstDay.DayOfWeek == 0)//如果第一天是周日，本年第一個周日=今天
 8   　　   yearFirstSunday = yearFirstDay;
 9 　 　else
10    　　 yearFirstSunday = yearFirstDay.AddDays(7 - (int)yearFirstDay.DayOfWeek);
11      NewWeekSunday = yearFirstSunday.AddDays((Convert.ToInt32(yearWeekindex.Substring(4)) - 1) * 7);//得第個星期的周日
12 　 　return NewWeekSunday.AddDays(-6).ToString("yyyyMMdd") + "～?" + NewWeekSunday.ToString("yyyyMMdd");
13  }

二、SQL時間函數(shù)總結(jié)：

　　1、SQL Server中主要的時間函數(shù)有：

　　①getdate()：返回當前系統(tǒng)日期、時間

select 當前系統(tǒng)時間=GETDATE()

　　②dateadd(interval,number,date)：返回指定日期加一段時間后的新datetime。

select 倒退一天=DATEADD(day,-1,getdate()), 前進一小時=DATEADD(hour,1,getdate())

　　③datediff(interval,date1,date2): 返回跨兩個指定時間的差值。

1  DATEDIFF(DAY,'2011-12-5','2012-1-1')       --返回：27
2 
3 select 天數(shù)=datediff(DAY,'2012-01-01','2013-01-01'),  月數(shù)=datediff(MONTH,'2013-01-01','2012-01-01')

　　④datepart(interval,date)和datename(interval,date):都是返回指定日期interval部分的值，不同的是，datepart()返回int類型的整數(shù)，

datename返回nvarchar類型的字符串。

1 DATEPART(YEAR,'2011-12-5')  -–datepart()返回：2011 (int類型)
2 DATENAME (YEAR,'2011-12-5') -–datename()返回：2011 (nvarchar類型)
3 
4 DATEPART(MONTH,'2011-12-5') -–datepart()返回：12    (int類型)
5 DATENAME(MONTH,'2011-12-5') –-datename()返回：December(nvarchar類型)

　　在此，把不同參數(shù)interval羅列出來，我們可以更清晰地看出，datepart()和datename()的區(qū)別。

1 select '當前時間(datepart)'=GETDATE(), 年=DATEPART(YEAR,GETDATE()),季=DATEPART(QUARTER,GETDATE()),月=DATEPART(Month,GETDATE()),
2 周=DATEPART(WEEK,GETDATE()),周日數(shù)=DATEPART(WEEKDAY,GETDATE()),日=DATEPART(DAY,GETDATE()),
3 年日數(shù)=DATEPART(DAYOFYEAR,GETDATE()),時=DATEPART(HOUR,GETDATE()),分=DATEPART(MINUTE,GETDATE()),
4 秒=DATEPART(SECOND,GETDATE()),毫秒=DATEPART(MILLISECOND,GETDATE())
5 
6 select '當前時間(datename)'=GETDATE(), 年=DATENAME(YEAR,GETDATE()),季=DATENAME(QUARTER,GETDATE()),月=DATENAME(Month,GETDATE()),
7 周=DATENAME(WEEK,GETDATE()),周日數(shù)=DATENAME(WEEKDAY,GETDATE()),日=DATENAME(DAY,GETDATE()),
8 年日數(shù)=DATENAME(DAYOFYEAR,GETDATE()),時=DATENAME(HOUR,GETDATE()),分=DATENAME(MINUTE,GETDATE()),
9 秒=DATENAME(SECOND,GETDATE()),毫秒=DATENAME(MILLISECOND,GETDATE())

　　⑤year(), month(),day()：返回指定日期的年、月、日。

1 YEAR('2011-12-5')=DATEPART(YEAR,'2011-12-5') --返回：2011
2 MONTH('2011-12-5')= DATEPART(Month,'2011-12-5') --返回：12
3 DAY('2011-12-5')= DATEPART(DAY,'2011-12-5') --返回：5

2、時間函數(shù)的interval參數(shù)及其縮寫

	縮寫（Sql Server）	Access 和 ASP	說明
Year	Yy	yyyy	年 (1753 ~ 9999)
Quarter	Qq	q	季 (1 ~ 4)
Month	Mm	m	月 (1 ~ 12)
Week	Wk	ww	周(一年中的第幾周 0 ~ 51)
Weekday	Dw	w	一周的日數(shù)，一周中第幾日 1-7
Day	Dd	d	日，1-31
Day of year	Dy	y	一年的日數(shù),一年中第幾日 1-366
Hour	Hh	h	時0 ~ 23
Minute	Mi	n	分鐘0 ~ 59
Second	Ss	s	秒 0 ~ 59
Millisecond	Ms	-	毫秒 0 ~ 999

3、日期格式轉(zhuǎn)化

1 select CONVERT(varchar, getdate(), 120 )   　　　　　  --2011-12-05 15:54:48
2 select CONVERT(varchar(12) , getdate(), 111 )  　　　  --2011/12/05
3 select CONVERT(varchar(12) , getdate(), 102 ) 　　　　 --2011.12.05 
4 select CONVERT(varchar(12) , getdate(), 112 ) 　　　　 --20111205
5 select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','') --20111205155448

三、C#中時間字符串的格式轉(zhuǎn)化

　　日期格式：yyyyMMdd HH:mm:ss (注意大小寫)

 1 DateTime dt = DateTime.Now;
 2 timestr = dt.ToString();            　　　　 //2011-12-05 16:32:26
 3 timestr = dt.ToShortDateString();    　　　  //2005-11-5
 4 timestr = dt.ToShortTimeString();     　　　 //16:32
 5 timestr = dt.ToLongDateString();     　　　　//2011-12-05
 6 timestr = dt.ToLongTimeString();    　　　　 //16:32:26
 7 timestr = string.Format("{0:yyyy-HH-dd HH:mm:ss}", dt);//2011-12-05 16:32:26
 8 timestr = string.Format("{0:yyyy/HH/dd}", dt);         //2011/12/05
 9 timestr = string.Format("{0:f}", dt);       //2011年12月5日 16:32
10 timestr = string.Format("{0:F}", dt);       //2011年12月5日 16:32:26
11 timestr = string.Format("{0:g}", dt);       //2011/12/5 16:32
12 timestr = string.Format("{0:G}", dt);       //2011/12/5 16:32:26
13 
14 dt.AddYears(1).ToString();               //增加1年
15 dt.AddMonths(1).ToString();              //增加1月
16 dt.AddDays(1).ToString();                //增加1天
17 dt.AddHours(1).ToString();               //增加1小時
18 dt.AddMinutes(1).ToString();             //增加1分鐘
19 dt.AddSeconds(1).ToString();             //增加1秒
20 dt.AddMilliseconds(1).ToString();        //增加1毫秒

//去重復(fù)
delete from friend where id in (select id from
(select id,userid, row_number() over(partition by userid order by id) as rn from friend ) as a where rn > 1)