对时间函数、字符串格式的一些总结

如果没有文字这种记录形式，日子一天天地过去，真也就过去了。我不时会听到一些朋友抱怨说，以前某某事情明明知道怎么解决的，

现在冥思苦想也回忆不起来了。此时我就会想说，既然互联网上找不到解决方案，以前你又知道该怎么做，为什么当时没有写一篇博

客记录下来呢？          

                                                                                                            ——摘自：Jeffrey Zhao的博客二三事

引言

　　　由于刚从校园里出来，经验尚浅加上文笔不甚好，所以久久没能写出一篇博客出来。上周公司安排的工作主要是报表这块，用到了一些关于时间方面

的函数，这些东西简单但杂碎，浪费了我很多时间，所以我把其整理记录下来，以备后用。

 

一、年月周统计DEMO

　　公司正式数据库比较复杂，加上表内数据不便轻易修改，所以在项目之前，我新建了个数据表用来测试代码的可行性，正式项目是体力活，但知识点

就这些。

 

　　图是一个存放康城小区、红河小区2012年1月份的水电费表(项目中，这是一多张表组成的临时表，字段要多一些，但表结构一致)。

　　项目的要求是：按照年、月、周、日分别对其进行统计，按日统计比较简单，不再累述。

 

　　为了方便测试，我在表内添加两条新数据。

insert into EnergySettings(PointID,ValueTime,BuildName,FieldName,Value) values('A','2011-12-31','康城小区','电费','100')

insert into EnergySettings(PointID,ValueTime,BuildName,FieldName,Value) values('A','2012-2-1','红河小区','水费','100')

　　此时表内，含有2011年12月一条数据，2012年1月31*4条数据，和2012年2月一条数据。

 

1按年统计(用到YEAR()函数)

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

 

2按月统计(用到MONTH()函数)

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

 

3按周统计(用到DATEPART()函数)

　　按周统计需要注意两个细节：

　　①   中西方观念不同，西方把周日作为一周的开始，我们习惯把周一作为一周的第一天。在SQL Server中，默认周日为一周的开始，所以我们需要根

据业务需求(set datefirst )来对一周的第一天是星期一还是星期日进行设置。

　　②   本年的第一个周和去年的最后一周是分开的，比如， 2011-12-31是星期六，2012-01-01是星期日，如果我们设置周一为一周的第一天，那

么2011-12-31是2011年第53周，2012-01-01是2012年第一周，这两周分开，也就是说2011年第53周有6天，2012年第一周只有1天。并不能笼

统地说2011年第53周就是2012年第一周。

 

　　以康城小区的电费为例：

　　默认下，周日为周起始：

select PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime) as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
 from EnergySettings where BuildName='康城小区'and FieldName='电费'
group by PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime),BuildName,FieldName

 

　　我们可以设置周一为一周开始：

set datefirst 1   --设置周一为一周的开始/datefirst 7=周日为一周开始
select PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime) as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
from EnergySettings where BuildName='康城小区'and FieldName='电费'group by PointID,YEAR(ValueTime)*100+DATEPART(w
eek,ValueTime),BuildName,FieldName

 

　　项目界面展示的时候，我们可以用下面GetNewWeekDays()方法把 201202转化为20120102～20120108这种格式(此方法在单独一年可用，

连续多年时需稍作修改，原因就是上面我们提到的细节②)。

/// <summary>
/// 根据传值得周一到周日日期 
/// </summary>
private string GetNewWeekDays(string yearWeekindex)
 {
 　　DateTime yearFirstDay = Convert.ToDateTime(yearWeekindex.Substring(0, 4) + "-01-01");//获得本年第一天的日期
　　 if (yearFirstDay.DayOfWeek == 0)//如果第一天是周日，本年第一个周日=今天
  　　   yearFirstSunday = yearFirstDay;
　 　else
   　　 yearFirstSunday = yearFirstDay.AddDays(7 - (int)yearFirstDay.DayOfWeek);
     NewWeekSunday = yearFirstSunday.AddDays((Convert.ToInt32(yearWeekindex.Substring(4)) - 1) * 7);//得第个星期的周日
　 　return NewWeekSunday.AddDays(-6).ToString("yyyyMMdd") + "～?" + NewWeekSunday.ToString("yyyyMMdd");
 }

 

二、SQL时间函数总结：

 

　　1、SQL Server中主要的时间函数有：

　　①getdate()：返回当前系统日期、时间

select 当前系统时间=GETDATE()

 

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

 

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

 

　　③datediff(interval,date1,date2): 返回跨两个指定时间的差值。

 DATEDIFF(DAY,'2011-12-5','2012-1-1')       --返回：27

select 天数=datediff(DAY,'2012-01-01','2013-01-01'),  月数=datediff(MONTH,'2013-01-01','2012-01-01')

 

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

datename返回nvarchar类型的字符串。

DATEPART(YEAR,'2011-12-5')  -–datepart()返回：2011 (int类型)
DATENAME (YEAR,'2011-12-5') -–datename()返回：2011 (nvarchar类型)

DATEPART(MONTH,'2011-12-5') -–datepart()返回：12    (int类型)
DATENAME(MONTH,'2011-12-5') –-datename()返回：December(nvarchar类型)

　　

　　在此，把不同参数interval罗列出来，我们可以更清晰地看出，datepart()和datename()的区别。

select '当前时间(datepart)'=GETDATE(), 年=DATEPART(YEAR,GETDATE()),季=DATEPART(QUARTER,GETDATE()),月=DATEPART(Month,GETDATE()),
周=DATEPART(WEEK,GETDATE()),周日数=DATEPART(WEEKDAY,GETDATE()),日=DATEPART(DAY,GETDATE()),
年日数=DATEPART(DAYOFYEAR,GETDATE()),时=DATEPART(HOUR,GETDATE()),分=DATEPART(MINUTE,GETDATE()),
秒=DATEPART(SECOND,GETDATE()),毫秒=DATEPART(MILLISECOND,GETDATE())

select '当前时间(datename)'=GETDATE(), 年=DATENAME(YEAR,GETDATE()),季=DATENAME(QUARTER,GETDATE()),月=DATENAME(Month,GETDATE()),
周=DATENAME(WEEK,GETDATE()),周日数=DATENAME(WEEKDAY,GETDATE()),日=DATENAME(DAY,GETDATE()),
年日数=DATENAME(DAYOFYEAR,GETDATE()),时=DATENAME(HOUR,GETDATE()),分=DATENAME(MINUTE,GETDATE()),
秒=DATENAME(SECOND,GETDATE()),毫秒=DATENAME(MILLISECOND,GETDATE())

 

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

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

 

2、时间函数的interval参数及其缩写

 

	缩 写（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	一周的日数，一周中第几日 1-7
Day	Dd	d	日，1-31
Day of year	Dy	y	一年的日数,一年中第几日 1-366
Hour	Hh	h	时0 ~ 23
Minute	Mi	n	分钟0 ~ 59
Second	Ss	s	秒 0 ~ 59
Millisecond	Ms	-	毫秒 0 ~ 999

 

3、日期格式转化

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

 

三、C#中时间字符串的格式转化

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

DateTime dt = DateTime.Now;
timestr = dt.ToString();            　　　　 //2011-12-05 16:32:26
timestr = dt.ToShortDateString();    　　　  //2005-11-5
timestr = dt.ToShortTimeString();     　　　 //16:32
timestr = dt.ToLongDateString();     　　　　//2011-12-05
timestr = dt.ToLongTimeString();    　　　　 //16:32:26
timestr = string.Format("{0:yyyy-HH-dd HH:mm:ss}", dt);//2011-12-05 16:32:26
timestr = string.Format("{0:yyyy/HH/dd}", dt);         //2011/12/05
timestr = string.Format("{0:f}", dt);       //2011年12月5日 16:32
timestr = string.Format("{0:F}", dt);       //2011年12月5日 16:32:26
timestr = string.Format("{0:g}", dt);       //2011/12/5 16:32
timestr = string.Format("{0:G}", dt);       //2011/12/5 16:32:26

dt.AddYears(1).ToString();               //增加1年
dt.AddMonths(1).ToString();              //增加1月
dt.AddDays(1).ToString();                //增加1天
dt.AddHours(1).ToString();               //增加1小时
dt.AddMinutes(1).ToString();             //增加1分钟
dt.AddSeconds(1).ToString();             //增加1秒
dt.AddMilliseconds(1).ToString();        //增加1毫秒