Feeds:
Posts
Comments

Posts Tagged ‘Date’


/*
    There are many uses of date tables and it could becomes essential at other times.
    The below script creates a function that produces a daily date table with the passed start and end dates.

    There are also examples at ehe ver bottom
*/

create function [dbo].[udf_DailyDateTable]
      (@StartDate datetime, @EndDate datetime)         
returns @DailyDates table 
(
    DailyDatesID int identity,
    DailyDate date, 
    DayNumber int,
    DayName varchar(50),
    WeekNumber int,
    QuaterNumber int,
    MonthNumber int,
    MonthCalled varchar(50),
    YearNumber int
)
AS 
BEGIN
 --   ------------ Test -------------

	---- select * from [dbo].[udf_DailyDateTable] ('1 jan 2016', '5 jan 2016')

 --   declare @DailyDates table
 --   (
 --       DailyDatesID int identity,
	--   DailyDate date, 
	--   DayNumber int,
 --       DayName varchar(50),
 --       WeekNumber int,
	--   QuaterNumber int,
	--   MonthNumber int,
	--   MonthCalled varchar(50),
 --       YearNumber int
 --   )
     
 --   declare @StartDate date, @EndDate date
 --   set @StartDate = '15 sep 2010' -- GETDATE()
 --   set @EndDate = '19 nov 2011'
	
 --  ------------------------ 
 
    while (@StartDate <= @EndDate)
    begin
        insert into @DailyDates
        select	@StartDate DailyDate
			 , datename(day, @StartDate) DayName 
				, datename(dw, @StartDate) DayName 
                , datepart(week, @StartDate) WeekNumber
			 , DATEPART(qq , @StartDate) as QuarterValue 
                , datepart(month,@StartDate) MonthNumber
				, datename(month,@StartDate) MonthCalled
				, YEAR(@StartDate) YearNumber
         
        set @StartDate = dateadd(day, 1 ,@StartDate)
        --print @StartDate -- test ` 
    end
     
    --select * from @DailyDates -- test ` 
    return 
END


------------------------ Examples below ------------------------------ 

DECLARE @DateToProcess date = '28 jul 2016' -- getdate() -- this is generally todays date 

-- it is important to initialse @EndOfLastMonth first because that in turn is used in @YTDDateFrom. 
-- if this is run in january then the last year's dates will be used  
DECLARE @EndOfLastMonth date = DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,@DateToProcess) , 0)) -- End of previous month. All results should be upto this date not yesterday
DECLARE @YTDDateFrom date = cast('1 jan ' + str(year(@EndOfLastMonth)) as date) -- YTD stars from 1 Jan. Just add the requested year


-- MTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(@EndOfLastMonth)
						  

-- QTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.QuaterNumber = datepart(q,@EndOfLastMonth) 

-- YTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.YearNumber = datepart(yy,@DateToProcess) 

-- Yesterday 
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.DailyDate = dateadd(day, -1, @DateToProcess)

-- Month Of Yesterday
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess))


---- e.g. ------
/*
    ---------- Filter for dates ----------------
    and (CASE 
		  WHEN ddt.DailyDate = dateadd(day, -1, @DateToProcess) THEN 'Yes'  --- this and the next ones need to be before the otheres 
		  WHEN ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess)) THEN 'MoY' -- this may be becasue thiss is a derived table and @DateToProcess > @EndOfLastMonth
		  WHEN ddt.MonthNumber = month(@EndOfLastMonth) THEN 'MTD' 
		  WHEN ddt.QuaterNumber = datepart(q,@EndOfLastMonth) THEN 'QTD' 
		  WHEN ddt.YearNumber = datepart(yy,@DateToProcess) THEN 'YTD'   		  
    END ) = @XTD 
    --------------------------------------------
*/
------------ 


Read Full Post »

Date in different ways



--See date in differnet ways

declare @StartDate datetime, @EndDate datetime 
set @StartDate = '1 jan 2012'
set @EndDate = '30 may 2012'


declare @MonthInDates table 
(
	MonthInDatesID int identity,
	MonthCalled varchar(50),
	MonthNumber int,
	MonthYear int,
	MonthStart datetime,
	MonthEnd datetime,
	MonthPeriod varchar (50)
)

while (@StartDate < @EndDate)
begin 
	insert into @MonthInDates
	select datename(month,@StartDate)MonthCalled
			, datepart(month,@StartDate) MonthNumber
			, YEAR(@StartDate) MonthYear
			, convert(datetime, '1/' + str(MONTH(@StartDate),2) 
				+ '/' + str(YEAR(@StartDate) ,4), 103) MonthStart -- 103 = dd/mm/yyyy
			, DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0, @StartDate) + 1, 0)) MonthEnd
			, left(datename(month,@StartDate),3) + ' ' + datename(year,@StartDate)MonthPeriod
	
	set @StartDate = dateadd(month, 1 ,@StartDate)
end 

select * from @MonthInDates



Read Full Post »

/*
Description:    Returns all the Months (& Year) in the given range along with its Start and End Dates
Takes Month number FROM and month number TO and the year to calculate the monthly table
This is a Monthly Date Table For A Single Year
*/
CREATE FUNCTION dbo.udf_ReturnSelectedMonths (
	@MonthFrom INT
	,@MonthTo INT
	,@Year INT
	)
RETURNS @SelectedMonths TABLE (
	ID INT identity
	,MonthName VARCHAR(10)
	,MonthNumber INT
	,MonthYear INT
	,StartDate DATETIME
	,EndDate DATETIME
	)
AS
BEGIN
	---------====== Test Values ======
	--    declare @MonthFrom int, @MonthTo int, @Year int
	--    set @MonthFrom = 3
	--    set @MonthTo = 5
	--    set @Year = 2011
	--    declare @SelectedMonths table
	--    (
	--        ID int identity,
	--        MonthName varchar(10),
	--        MonthNumber int,
	--        MonthYear int,
	--        StartDate datetime,
	--        EndDate datetime
	--    )
	-------=============================
	DECLARE @NoOfMonths INT

	SET @NoOfMonths = (@MonthTo - @MonthFrom) + 1

	-------- Construct the starting month selected to construct the Start and End Dates of all the months
	DECLARE @PassedDate DATETIME

	SET @PassedDate = convert(DATETIME, '1/' + str(@MonthFrom, 2) + '/' + str(@year, 4), 103) -- 103 = dd/mm/yyyy

	DECLARE @count INT

	SET @count = 0

	WHILE (@count < @NoOfMonths)
	BEGIN
		INSERT @SelectedMonths
		SELECT datename(month, DATEADD(m, @count, @PassedDate))
			,-- MonthName
			datepart(month, DATEADD(m, @count, @PassedDate))
			,-- MonthNumber
			--datepart(year,DATEADD(m, @count, @PassedDate)), --MonthYear
			@Year
			,-- The year is alwasy fixed so there is no point in deucing that like above (this wil reduce the query time)
			DATEADD(m, @count, @PassedDate) [Beginning of previous month]
			,-- StartDate
			DATEADD(ms, - 3, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, @count, @PassedDate)) + 1, 0)) -- EndDate

		SET @count = @count + 1
	END

	-----------====== Test Values ======
	--    select * from @SelectedMonths
	---------=============================
	RETURN
END
	/*---------====== Test ======

Run the below statement once the above function is created to test

select * from udf_ReturnSelectedMonths (3, 5, 2011)

---------=============================*/

Follow the link below to find out how to use this function.

http://wp.me/pAchH-3T

Read Full Post »

/*

This Function provides all the Month Year and data that can be joined with another table for trend analysis

*/
-------====== Test Values ======
DECLARE @MonthFrom INT
	,@MonthTo INT
	,@Year INT
	,@SPName VARCHAR(100) -- Parameters

SET @MonthFrom = 1
SET @MonthTo = 3
SET @Year = 2011

-----=============================
/*
to use the function you have to make sure the table it will be joined to has a date field to be joined.
The date field can either hold
i) only the first day of the month -- If the row is coming from an aggregated source then you might nned to create this field and
by defualt only store the first day of the month

ii) or any day of the month - Depending on what this date column holds the below join can be altered for optimisation

*/
DECLARE @main TABLE (
	ID INT identity
	,DateToJoin DATETIME -- These has to
	)

INSERT @main
VALUES ('1 jan 2011')

INSERT @main
VALUES ('21 jan 2011')

INSERT @main
VALUES ('6 feb 2011')

SELECT * --MonthName, MonthNumber, MonthYear, StartDate, EndDate, Dimension1, Measure1--, Dimension2, Measure2, Dimension3, Measure3
FROM udf_ReturnSelectedMonths(@MonthFrom, @MonthTo, @Year) sm
INNER JOIN @Main mn
	-- on sm.StartDate = mn.DateToJoin ---- Use this is the start of the month date is availalbe else use the below
	ON mn.DateToJoin BETWEEN sm.StartDate
		AND sm.EndDate -- This join can always be used regardless whether The first of the mont his stored or not
ORDER BY 1
	-- select * from udf_ReturnSelectedMonths (3, 5, 2011)

How to contruct the udf_ReturnSelectedMonths:

http://wp.me/sAchH-254

Read Full Post »

-- Finding out the the start and end dates of the given month and year
DECLARE @Month INT, @Year INT

SELECT @Month = 1, @Year = 2011

------ ======= Initialize ==============
DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = convert(DATETIME, '1/' + str(@Month, 2) + '/' + str(@Year, 4), 103) -- 103 = dd/mm/yyyy
SET @EndDate = DATEADD(ms, - 3, DATEADD(m, DATEDIFF(m, 0, @StartDate) + 1, 0))

---------===========================
SELECT @StartDate, @EndDate
/*
SSRS

Parameters!DateFrom.Value =CDate(Str(Parameters!prmYear.Value) + "-" + Str(Parameters!prmMonthFrom.Value) + "-1") -- Beginning of the month (with a given year and month number)

Parameters!DateTo.Value =DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01", Parameters!DateFrom.Value)+1, "1900-01-01")) -- End of the month


-- End of a a different selected month

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01",

CDate(Str(Parameters!prmYear.Value) + "-" + Str(Parameters!prmMonthTo.Value) + "-1") -- >> This is similar to DateFrom but instead prmMonthTo is used

)+1, "1900-01-01")) -- End of the month


*/

Read Full Post »

DECLARE @StartDate DATETIME, @EndDate DATETIME, @CompanyID INT

SET @StartDate = '1 jan 2010'
SET @EndDate = '2 jan 2010'

SELECT DATEDIFF(d, @StartDate, @EndDate) + 1
	-- (@EndDate - @StartDate) = 2 

Read Full Post »

-- TSQL -- Fractions (or float) not being returnedTSQL -- Counting Nulls »TSQL, SSRS -- Date Manipulation
--July 19, 2010 by Usama 

SELECT getdate() Date1, getdate() + 1 Date2
     , DATEDIFF(day,GETDATE() + 1, getdate()) NegetiveFigure
	, DATEDIFF(day,GETDATE(), getdate() + 1) PositiveFigure_PlaceTheBiggerOnTheRight

Select dateadd(D, 0, datediff(D, 0, GETDATE())) -- Time Stripped (without any data type conversion for optimized performance)

Select DATEADD(m, DATEDIFF(m,0,GETDATE() )-1, 0) -- Beginning of previous month
select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) , 0)) -- End of previous month

/*
SSRS 

=DateAdd("m",DateDiff("m", "1900-01-01″, Today()) -1, "1900-01-01″) -- Beginning of previous month

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″)) -- End of previous month
*/

-----------------------------------------------------------------

Select DATEADD(m, DATEDIFF(m,0,GETDATE() ), 0) -- Beginning of the month

select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) + 1, 0)) -- End of the month 

/*
SSRS 

=DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″) -- Beginning of the month 

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today())+1, "1900-01-01″)) -- End of the month
*/ 

------------------------

-- Converting String to Datetime

declare @SelectedMonth int = 1, @year int = 2010 

-- Replace is not needed in the below line but is just illustrated if you want a leading 0 when the month is a single digit
declare @date datetime = convert(datetime, '28/' + replace(str(@SelectedMonth,2),' ‘,'0′) + ‘/' + str(@year ,4), 103)
select @date '


-------------------------------------- 

select 
DATENAME(weekday , getdate())
,  DATEADD(ms,-3,DATEADD(D, DATEDIFF(D,0,GETDATE()) , 0)) -- End of play > Yesterday 
, DATEADD(D, DATEDIFF(D,0,GETDATE())-1 , 0) -- Beginning of play > Yesterday 

Read Full Post »

Older Posts »