Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

Tagged as

Useful DateTime Functions

, 1 Sep 2011
Rate this:
Please Sign up or sign in to vote.
IF OBJECT_ID (N'dbo.GetIntervalStartDate', N'FN') IS NOT NULL DROP FUNCTION dbo.GetIntervalStartDate;GO--gets first day of interval date belongs toCREATE FUNCTION dbo.GetIntervalStartDate (@Date datetime,@IntervalType int = 0--0 - DAY, 1 - WEEK (Mon to Sun), 2 - MONTH, 3 -...
IF OBJECT_ID (N'dbo.GetIntervalStartDate', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetIntervalStartDate;
GO
--gets first day of interval date belongs to
CREATE FUNCTION dbo.GetIntervalStartDate (
@Date datetime
,@IntervalType int = 0
--0 - DAY, 1 - WEEK (Mon to Sun), 2 - MONTH, 3 - FYQUARTER, 4 - FYQUARTER
)
RETURNS datetime2
AS
BEGIN
    DECLARE @IntervalStartDate datetime;
    
    SELECT @IntervalStartDate = 
     
	(case @IntervalType
		when 0 then DATEADD(dd,DATEDIFF(dd,0,@Date),0)		--First Day of Current Day
		when 1 then DATEADD(wk,DATEDIFF(wk,0,@Date),0)		--First Day of Current Week
		when 2 then DATEADD(mm,DATEDIFF(mm,0,@Date),0)		--First Day of Current Month'
		when 3 then DATEADD(qq,DATEDIFF(qq,0,@Date),0)		--First Day of Current Quarter
		when 4 then DATEADD(qq,DATEDIFF(qq,0,@Date),0)		--First Day of Current Quarter
		else NULL
	end);
     
	RETURN(@IntervalStartDate);
END;
GO

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

db_developer
Database Developer Freelancer
Ukraine Ukraine
MS SQL Server Database Developer with 7+ years experience
 
Technologies/languages: Business Intelligence, SQL, MDX, VBA, SQL Server, Analysis Services (SSAS), Reporting services (SSRS), Integration Services (SSIS), DataWarehouse.
Also: economic background.
 
Feel free to contact me for rates and details.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 1 Sep 2011
Article Copyright 2011 by db_developer
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid