Lesson 54. Converting Dates To YYYYMMDD

The primary key of DimDate should be an INT value that is a YYYYMMDD representation of the date that row represents. Since dates rarely come into the data warehouse in this format, you will need to convert them.

Below is a function that allows you to clean dates to the YYYYMMDD format. This function is for processing warehouse data but can be repurposed.

Examples

Convert Date UDF

/*
Author: Bob Wakefield
Create date: 17Jun18
Description: Clean dates to conform with DimDateCK
*/

/*
Directions for use. 
1. Set @EmptyRecordCode and @ErrorDateCode to the values appropriate to your system. 
2. Find and replace YourDatabase.YourSchema.DimDate with the name of your data warehouse
and the schema that it lives in.
*/

USE demo

GO

DROP FUNCTION IF EXISTS udf_CleanDate

GO

CREATE FUNCTION udf_CleanDate(@DATE NVARCHAR(255))

RETURNS NCHAR(8)

AS

BEGIN

DECLARE @MinDate DATE
DECLARE @MaxDate DATE
DECLARE @EmptyRecordCode BIGINT = 00000000
DECLARE @ErrorDateCode BIGINT = 11111111

SELECT @MaxDate = CAST(MAX(DateCK) AS NCHAR(8))
FROM YourDatabase.YourSchema.DimDate
WHERE DateCK NOT IN (@EmptyRecordCode,@ErrorDateCode)

SELECT @MinDate = CAST(MIN(DateCK) AS NCHAR(8))
FROM YourDatabase.YourSchema.DimDate
WHERE DateCK NOT IN (@EmptyRecordCode,@ErrorDateCode)


--Set empty dates to the empty field code
--This HAS to be done before you check for
--bad dates. Then Set error dates to the error field code.
--Finally, clean up the dates to conform to DimDateCK format.

RETURN
CASE 
WHEN @Date IS NULL THEN  @EmptyRecordCode
WHEN ISDATE(@Date) = 0 THEN  @ErrorDateCode
WHEN @Date < @MinDate THEN  @ErrorDateCode
WHEN @Date > @MaxDate THEN  @ErrorDateCode
ELSE CONVERT(VARCHAR(10),CAST(@Date AS DATE),112)
END


END