MSBI (SSIS/SSRS/SSAS) Online Training

Thursday, August 26, 2010

Sql Server Useful Date() FUNC

/*
Many useful SQL Server Date functions.
*/
--DATE CONVERSION
SELECT CONVERT (date, CURRENT_TIMESTAMP) ,CONVERT (date, GETDATE()) ,CONVERT (date, GETUTCDATE());
--DATE ADD()
SELECT DATEADD(year,10, getdate());
SELECT DATEADD(month,10, getdate());
SELECT DATEADD(day,10, getdate());
--DATE DIFF()
select DATEDIFF (day,getdate() ,'2010-05-26')
select DATEDIFF (month,getdate() ,'2010-05-26')
--DATE NAME()
SELECT DATENAME(year, '12:10:30.123') ,DATENAME(month, '12:10:30.123') ,DATENAME(day, '12:10:30.123') ,
DATENAME(dayofyear, '12:10:30.123') ,DATENAME(weekday, '12:10:30.123') ,DATENAME(hour, '2007-06-01') ,
DATENAME(minute, '2007-06-01') ,DATENAME(second, '2007-06-01');
--DATE PART()
SELECT DATEPART(year, '12:10:30.123') ,DATEPART(month, '12:10:30.123') ,DATEPART(day, '12:10:30.123')
,DATEPART(dayofyear, '12:10:30.123') ,DATEPART(weekday, '12:10:30.123');
****************************************************************************
select DATEPART(dw, getdate()) AS 'Today';
SELECT CURRENT_TIMESTAMP ,GETDATE() ,GETUTCDATE();
select convert(varchar(12),getdate(),112) AS 'YYYYMMDD'
UNION ALL
select convert(varchar(12),getdate(),112) AS 'YYYYMMDD'
UNION ALL
select convert(varchar(10),getdate(),120) AS 'YYYY-MM-DD'
UNION ALL
SELECT DATENAME (MM,GETDATE())
****************************************************************************
****************************************************************************
----Today
SELECT GETDATE() 'Today'
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'

No comments: