Jeff McLellan | Data Analysis & App Developer
CREATE FUNCTION [dbo].[getSaturday] (@date datetime) RETURNS datetime AS BEGIN DECLARE @weekend datetime DECLARE @tempDate datetime SET @date = CAST(FLOOR(CAST(@date AS FLOAT)) AS DATETIME) IF datepart(weekday,@date) = 7 set @weekend = @date ELSE set @weekend = dateadd(dd, 7 - datepart(weekday,@date),@date) RETURN(@weekend) END
CREATE FUNCTION [dbo].[convertAS400DateToSQLDate_TEST] (@DATE int) RETURNS Datetime AS BEGIN DECLARE @sqlDate Datetime DECLARE @dateHolder varchar(20) IF LEN(CAST(@DATE AS varchar(20))) > 6 BEGIN SET @dateHolder = SUBSTRING(CAST(@DATE AS varchar(7)),4,2) + '/' + SUBSTRING(CAST(@DATE AS varchar(7)),6,2) + '/' + SUBSTRING(CAST(@DATE AS varchar(7)),2,2) IF ISDATE(@dateHolder) = 1 SET @sqlDate = CAST( SUBSTRING(CAST(@DATE AS varchar(7)),4,2) + '/' + SUBSTRING(CAST(@DATE AS varchar(7)),6,2) + '/' + SUBSTRING(CAST(@DATE AS varchar(7)),2,2) AS DateTime); ELSE SET @sqlDate = null END ELSE BEGIN SET @dateHolder = SUBSTRING(CAST(@DATE AS varchar(7)),3,2) + '/' + SUBSTRING(CAST(@DATE AS varchar(7)),5,2) + '/19' + SUBSTRING(CAST(@DATE AS varchar(7)),1,2) IF ISDATE(@dateHolder) = 1 SET @sqlDate = CAST( SUBSTRING(CAST(@DATE AS varchar(7)),3,2) + '/' + SUBSTRING(CAST(@DATE AS varchar(7)),5,2) + '/19' + SUBSTRING(CAST(@DATE AS varchar(7)),1,2) AS DateTime); ELSE SET @sqlDate = null END RETURN(@sqlDate) END
create FUNCTION [dbo].[convertSQLDateToAS400Date] (@DATE datetime) RETURNS int AS BEGIN DECLARE @as400 varchar(7) SET @as400 = '1' + SUBSTRING(CAST(DATEPART(yy, @DATE) AS varchar(4)),3,2) + ( case LEN(CAST(DATEPART(mm, @DATE) AS varchar(2))) when 1 then '0' + CAST(DATEPART(mm, @DATE) AS varchar(2)) else CAST(DATEPART(mm, @DATE) AS varchar(2)) end ) + ( case LEN(CAST(DATEPART(dd, @DATE) AS varchar(2))) when 1 then '0' + CAST(DATEPART(dd, @DATE) AS varchar(2)) else CAST(DATEPART(dd, @DATE) AS varchar(2)) end ) RETURN(CAST(@as400 AS int)) END
CREATE FUNCTION [dbo].[getPreviousBizDay] (@date datetime) RETURNS datetime AS BEGIN DECLARE @bizDay datetime SET @date = CAST(FLOOR(CAST(@date AS FLOAT))AS DATETIME) IF datepart(weekday,@date) = 1 set @bizDay = dateadd(dd,-2,@date) ELSE IF datepart(weekday,@date) = 7 set @bizDay = dateadd(dd,-1,@date) ELSE set @bizDay = @date RETURN(@bizDay) END
CREATE FUNCTION [dbo].[getCutoffDate] ( @date Datetime ) RETURNS Datetime AS BEGIN DECLARE @cutoffEnd Datetime IF DATEPART(dd,@date) >= 19 SET @date = DATEADD(m,+1,@date) SET @cutoffEnd = DATEADD(d,18 - DATEPART(dd,@date),@date) RETURN CAST(FLOOR(CAST(@cutoffEnd AS FLOAT)) AS DATETIME) END
Create FUNCTION [dbo].[getCutOffDate] ( @date Datetime, @cutoff int ) RETURNS Datetime AS BEGIN DECLARE @cutoffDate Datetime IF DATEPART(dd,@date) >= @cutoff - 1 SET @date = DATEADD(m,+1,@date) SET @cutoffDate = DATEADD(d,@cutoff - DATEPART(dd,@date),@date) RETURN @cutoffDate END select [dbo].[getCutOffDate]('8/21/2015',18) result: 9/18/2015 // sets cutoff date to next 18th of month