【SQL】SQL抓月初月尾

select  year(  GETDATE()) --目前年
select convert(varchar(10),getdate(),111)--月底
select dateadd(month,datediff(month,'1900-01-01',getdate()),'1900-01-01') --當月初
select  dateadd(month,datediff(month,0,getdate()),0)  --當月初
select dateadd(month,datediff(month,-1,getdate()),-1)--當月底
SELECT DATEADD(MM, DATEDIFF(MM,0,(SELECT DATEADD(MM,-1, GETDATE()))),0)    --上個月初 

【SQL】依當年度週別取得日期

--MSSQL
with ach as
(
 select dateadd(dd,number,cast(ltrim(year(getdate()))+'-01-01' as datetime)) date
 from master..spt_values
 where [type] = 'p' and number between 0 and 400 
),cte as
(
 select *
 from ach
 where year(date) = year(getdate())
), abc as
(
select datepart(wk,date) as wk,min(date)  mindate,max(date)  maxdate
from cte
group by datepart(wk,date)
)


select CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), -1),112 ) as int ) as 'SUNDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 0),112 ) as int ) as 'MONDAY' 

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 1),112 ) as int ) as 'TUESDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 2),112 ) as int ) as 'WENDNESDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 3),112 ) as int ) as 'THURSDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 4),112 ) as int ) as 'FRIDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 5),112 ) as int ) as 'SATURDAY'
from abc
where wk='1' -- 自行代入週別 

【C#】DATETIME心得

1.yyyyMMdd的字串轉DateTime

var newDate = DateTime.ParseExact("20111120", "yyyyMMdd", CultureInfo.InvariantCulture);

或

string str = "20111021";
string[] format = {"yyyyMMdd"};
DateTime date;

if (DateTime.TryParseExact(str, format, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out date))
{
     //valid
}

2.常用函數

C# 常用日期時間函數（老用不熟） ，需要的朋友可以參考下。
//取當前年月日時分秒
currentTime=System.DateTime.Now;
//取當前年
int 年=currentTime.Year;
// 取當前月
int 月=currentTime.Month;
// 取當前日
int 日=currentTime.Day;
// 取當前時
int 時=currentTime.Hour;
//取當前分
int 分=currentTime.Minute;
// 取當前秒
int 秒=currentTime.Second;
// 取當前毫秒
int 毫秒=currentTime.Millisecond;

// 取中文日期顯示——年月日時分
string strY=currentTime.ToString("f"); //不顯示秒
// 取中文日期顯示_年月
string strYM=currentTime.ToString("y");
//取中文日期顯示_月日
string strMD=currentTime.ToString("m");
// 取當前年月日，格式為：2003-9-23
string strYMD=currentTime.ToString("d");
// 取當前時分，格式為：14：24
string strT=currentTime.ToString("t");
//今天
DateTime.Now.Date.ToShortDateString();
//昨天，就是今天的日期減一
DateTime.Now.AddDays(-1).ToShortDateString();
//明天
DateTime.Now.AddDays(1).ToShortDateString();
//本周(要知道本周的第一天就得先知道今天是星期幾，從而得知本周的第一天就是幾天前的//那一天，要注意的是這裏的每一周是從周日始至週六止
DateTime.Now.AddDays(Convert.ToDouble((0 - Convert.ToInt16(DateTime.Now.DayOfWeek)))).ToShortDateString();
DateTime.Now.AddDays(Convert.ToDouble((6 - Convert.ToInt16(DateTime.Now.DayOfWeek)))).ToShortDateString();
//如果你還不明白，再看一下中文顯示星期幾的方法就應該懂了
//由於DayOfWeek返回的是數字的星期幾，我們要把它轉換成漢字方便我們閱讀，有些人可能會用switch來一個一個地對照，其實不用那麼麻煩的 string[] Day = new string[] { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
Day[Convert.ToInt16(DateTime.Now.DayOfWeek)];
//上周，同理，一個周是7天，上周就是本周再減去7天，下周也是一樣
DateTime.Now.AddDays(Convert.ToDouble((0 - Convert.ToInt16(DateTime.Now.DayOfWeek))) - 7).ToShortDateString();
DateTime.Now.AddDays(Convert.ToDouble((6 - Convert.ToInt16(DateTime.Now.DayOfWeek))) - 7).ToShortDateString();
//下周
DateTime.Now.AddDays(Convert.ToDouble((0 - Convert.ToInt16(DateTime.Now.DayOfWeek))) + 7).ToShortDateString();
DateTime.Now.AddDays(Convert.ToDouble((6 - Convert.ToInt16(DateTime.Now.DayOfWeek))) + 7).ToShortDateString();
//本月,很多人都會說本月的第一天嘛肯定是1號，最後一天就是下個月一號再減一天。當然//這是對的
//一般的寫法
DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + "1"; //第一天
DateTime.Parse(DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + "1").AddMonths(1).AddDays(-1).ToShortDateString();//最後一天