日期处理 技巧整理

/*******************************************************************************************************************
* 基础部分
********************************************************************************************************************/

--GETDATE 返回当前系统日期和时间。
/* A. 返回当前日期和时间 */                        SELECT GETDATE();
/* B. CREATE TABLE 中GETDATE作为默认值 */        CREATE TABLE employees( emp_hire_date datetime DEFAULT GETDATE() );

--DATEADD( datepart , number, date ) 返回给指定日期加上一个时间间隔后的新 datetime 值。
/* 年                year, yy, yyyy */            SELECT DATEADD(year, 1, getdate());
/* 季度                quarter, qq, q */            SELECT DATEADD(quarter, 1, getdate());
/* 月                month, mm, m */                SELECT DATEADD(month, 1, getdate());
/* 天                dayofyear, dy, y */            SELECT DATEADD(dayofyear, 1, getdate());
/* 天                day, dd, d */                SELECT DATEADD(day, 1, getdate());
/* 周                week, wk, ww */                SELECT DATEADD(week, 1, getdate());
/* 天                weekday, dw, w */            SELECT DATEADD(weekday, 1, getdate());
/* 小时                hour, hh */                    SELECT DATEADD(hour, 1, getdate());
/* 分钟                minute, mi, n */            SELECT DATEADD(minute, 1, getdate());
/* 秒                second, ss, s */            SELECT DATEADD(second, 1, getdate());
/* 毫秒                millisecond, ms */            SELECT DATEADD(millisecond, 1, getdate());

--DATEDIFF( datepart , startdate , enddate ) 返回跨两个指定日期的日期边界数和时间边界数。
/* 年                year, yy, yyyy */            SELECT DATEDIFF(year, '2011-01-01', '2012-01-02');
/* 季度                quarter, qq, q */            SELECT DATEDIFF(quarter, '2011-01-01', '2012-01-02');
/* 月                month, mm, m */                SELECT DATEDIFF(month, '2011-01-01', '2012-01-02');
/* 天                dayofyear, dy, y */            SELECT DATEDIFF(dayofyear, '2011-01-01', '2012-01-02');
/* 天                day, dd, d */                SELECT DATEDIFF(day, '2011-01-01', '2012-01-02');
/* 周                week, wk, ww */                SELECT DATEDIFF(week, '2012-01-01', '2012-01-12');
/* 小时                Hour, hh */                    SELECT DATEDIFF(Hour, '2012-01-01 01:00', '2012-01-02 02:00');
/* 分钟                minute, mi, n */            SELECT DATEDIFF(minute, '2012-01-01 01:01', '2012-01-01 02:03');
/* 秒                second, ss, s */            SELECT DATEDIFF(second, '2012-01-01 01:00:05', '2012-01-01 01:01:04');
/* 毫秒                millisecond, ms */            SELECT DATEDIFF(millisecond, '2012-01-01 01:00:05.623', '2012-01-01 01:00:05.806');

--DATEPART ( datepart , date ) 返回表示指定日期的指定日期部分的整数。
/* 年                year, yy, yyyy */            SELECT DATEPART(year, GETDATE());
/* 季度                quarter, qq, q */            SELECT DATEPART(quarter, GETDATE());
/* 月                month, mm, m */                SELECT DATEPART(month, GETDATE());
/* 年的第几天        dayofyear, dy, y */            SELECT DATEPART(dayofyear, GETDATE());
/* 月的第几天        day, dd, d */                SELECT DATEPART(day, GETDATE());
/* 年的第几周        week, wk, ww */                SELECT DATEPART(week, GETDATE());
/* 周的第几天(周日)    weekday, dw */                SELECT DATEPART(weekday, GETDATE());
/* 小时                Hour, hh */                    SELECT DATEPART(Hour, GETDATE());
/* 分钟                minute, mi, n */            SELECT DATEPART(minute, GETDATE());
/* 秒                second, ss, s */            SELECT DATEPART(second, GETDATE());
/* 毫秒                millisecond, ms */            SELECT DATEPART(millisecond, GETDATE());

--DATENAME 返回表示指定日期的指定日期部分的字符串。
/* 年                year, yy, yyyy */            SELECT DATENAME(year, GETDATE());
/* 季度                quarter, qq, q */            SELECT DATENAME(quarter, GETDATE());
/* 月(补零)            month, mm, m */                SELECT DATENAME(month, GETDATE());
/* 年的第几天        dayofyear, dy, y */            SELECT DATENAME(dayofyear, GETDATE());
/* 月的第几天        day, dd, d */                SELECT DATENAME(day, GETDATE());
/* 年的第几周        week, wk, ww */                SELECT DATENAME(week, GETDATE());
/* 星期几            weekday, dw */                SELECT DATENAME(weekday, GETDATE());
/* 小时                Hour, hh */                    SELECT DATENAME(Hour, GETDATE());
/* 分钟                minute, mi, n */            SELECT DATENAME(minute, GETDATE());
/* 秒                second, ss, s */            SELECT DATENAME(second, GETDATE());
/* 毫秒                millisecond, ms */            SELECT DATENAME(millisecond, GETDATE());

--DAY( date ), MONTH( date ), YEAR( date )
SELECT DAY(GETDATE()),DATEPART(day, GETDATE());
SELECT MONTH(GETDATE()),DATEPART(month, GETDATE());
SELECT YEAR(GETDATE()),DATEPART(year, GETDATE());


/*******************************************************************************************************************
* 日期转换,案例来自实际工作中的积累和网络收集
* http://www.2cto.com/database/201204/126110.html
********************************************************************************************************************/
/* 2012-04-06 */                
SELECT GETDATE() AS Val,Convert(char(11),GETDATE(),120) AS ConvertVal
/* 20120406 */                    
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 112 ) AS ConvertVal
/* 2012.04.06 */                
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 102 ) AS ConvertVal
/* 04/06/2012 月日年 */            
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 101 ) AS ConvertVal
/* 06/04/2012 日月年 */            
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 103 ) AS ConvertVal
/* 06.04.2012 日月年 */            
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 104 ) AS ConvertVal
/* 06-04-2012 日月年 */            
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 105 ) AS ConvertVal
/* 06 Apr 2012 日月年 */        
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 106 ) AS ConvertVal
/* Apr 06, 2012 */                
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 107 ) AS ConvertVal
/* 15:49:02 */                    
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 108 ) AS ConvertVal
/* Apr  6 2012  */                
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 109 ) AS ConvertVal
/* 04-06-2012 */                
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 110 ) AS ConvertVal
/* 2012/04/06 */                
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 111 ) AS ConvertVal
/* 20120406 */                    
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 112 ) AS ConvertVal
/* 06 Apr 2012  */                
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 113 ) AS ConvertVal
/* 15:49:23:717 */                
SELECT GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 114 ) AS ConvertVal
/* 2012-4-6 */                    
SELECT GETDATE() AS Val,REPLACE(CONVERT(varchar(10),GETDATE(),120),N'-0','-') AS ConvertVal
/* 2012年04月06日 */            
SELECT GETDATE() AS Val,STUFF(STUFF(CONVERT(char(8),GETDATE(),112),5,0,N'年'),8,0,N'月')+N'日' AS ConvertVal
/* 2012年4月6日 */                
SELECT GETDATE() AS Val,DATENAME(Year,GETDATE())+N'年'+CAST(DATEPART(Month,GETDATE()) AS varchar)+N'月'+DATENAME(Day,GETDATE())+N'日' AS ConvertVal
/* 2012-04-06 15:49:38:077 */    
SELECT GETDATE() AS Val,CONVERT(char(11),GETDATE(),120)+CONVERT(char(12),GETDATE(),114) AS ConvertVal

 

/*******************************************************************************************************************
* 日期推算,案例来自实际工作中的积累和网络收集
* http://www.2cto.com/database/201204/126110.html
******************************************************************************************************************* */
/* 今年的第一天和最后一天 */       
SELECT CONVERT(char(5),GETDATE(),120)+'1-1' AS FirstDate,CONVERT(char(5),GETDATE(),120)+'12-31' AS LastDate
/* 当前季度的第一天和最后一天 */
SELECT
CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE())-2,GETDATE()),120)+'1') AS FirstDate
,CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120)+CASE WHEN DATEPART(Quarter,GETDATE()) in(1,4) THEN '31'ELSE '30' END) AS LastDate
SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE())-2,GETDATE()),120)+'1') AS FirstDate
,DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120)+'1') AS LastDate
/* 所在月份的第一天和最后一天 */
SELECT CONVERT(datetime,CONVERT(char(8),GETDATE(),120)+'1') AS FirstDate
,DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,GETDATE()),120)+'1') AS LastDate
/* 所在周的第一天和最后一天 */
SELECT a.FirstDay,b.EndDay FROM (     
    SELECT 1 AS ID,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AS FirstDAy
) a
LEFT JOIN (
    SELECT 1 AS ID,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 6) AS EndDay
) b
ON a.ID = b.ID
/* 所在周的取星期几的日期,星期天做为一周的第1天 */
SELECT DATEADD(Day,0-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,1-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,2-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,3-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,4-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,5-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,6-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())

转载于:https://www.cnblogs.com/Frankabc/archive/2012/04/06/SQLServer_%e6%97%a5%e6%9c%9f%e5%a4%84%e7%90%86_Date.html