[NOTE this is my old page saved for enjoyment of future generations, many links dead]

Jeff McLellan | Data Analysis & App Developer

SQL Functions

Pass in a date and receive the Saturday date for that week - used to set weekly reporting dates


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
Convert AS400 date (1150608) to SQL date (6/8/15)


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
Convert SQL date (6/8/15) to AS400 date (1150608) (year 2000+)

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
Determine the last business day (if Sat -> Fri, if Sun -> Fri)

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
Determine the upcoming Cutoff Date for date - in example the cutoff is the 18th of each month

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
Or pass in the cutoff date
 
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

www.lucidsyntax.com