Transforming and verifying dates
-
I am probabely be called an idiot rom now on but I can't figure this out, tried everything. Here I have to trasfer in SQL a very disfunctionnal FMP database. This is the function that verifies the dates. The problem I have is this: It works perfectly when the original date (formatted 01/01/1800) is correct but the last part should overwrite any malformed date (anyways that was the intention....) but none get overwritten. I always get the malformed date back. It is as if @DateTest never gets put to False. Help!!:confused: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetBirthDate] ( @BirthDate nvarchar(10) ) RETURNS nvarchar(10) AS BEGIN DECLARE @ReturnValue nvarchar(10) DECLARE @DateTest bit SET @DateTest = 'True' IF (@BirthDate = '00/00/00' OR @BirthDate = '' OR @BirthDate = '?' OR @BirthDate IS NULL) SET @ReturnValue = '1800-01-01' ELSE BEGIN IF LEN(@BirthDate) = 10 SET @ReturnValue = SUBSTRING(@BirthDate, 7, 4) + '-' + SUBSTRING(@BirthDate, 4, 2) + '-' + LEFT(@BirthDate, 2) ELSE SET @ReturnValue = '19' + SUBSTRING(@BirthDate, 7, 2) + '-' + SUBSTRING(@BirthDate, 4, 2) + '-' + LEFT(@BirthDate, 2) END IF (SUBSTRING(@ReturnValue, 1, 2) <> '19' AND SUBSTRING(@ReturnValue, 1, 2) <> '20') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 3, 1) < '0' OR SUBSTRING(@ReturnValue, 3, 2) > '9') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 4, 1) < '0' OR SUBSTRING(@ReturnValue, 4, 2) > '9') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 6, 1) < '0' OR SUBSTRING(@ReturnValue, 6, 2) > '1') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 7, 1) < '0' OR SUBSTRING(@ReturnValue, 7, 2) > '9') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 9, 1) < '0' OR SUBSTRING(@ReturnValue, 9, 2) > '3') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 10, 1) < '0' OR SUBSTRING(@ReturnValue, 10, 2) > '9') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 5, 1) <> '-') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 8, 1) <> '-') SET @DateTest = 'False' IF @DateTest = 'False' SET @ReturnValue = '1800-01-01' RETURN @ReturnValue END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
-
I am probabely be called an idiot rom now on but I can't figure this out, tried everything. Here I have to trasfer in SQL a very disfunctionnal FMP database. This is the function that verifies the dates. The problem I have is this: It works perfectly when the original date (formatted 01/01/1800) is correct but the last part should overwrite any malformed date (anyways that was the intention....) but none get overwritten. I always get the malformed date back. It is as if @DateTest never gets put to False. Help!!:confused: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetBirthDate] ( @BirthDate nvarchar(10) ) RETURNS nvarchar(10) AS BEGIN DECLARE @ReturnValue nvarchar(10) DECLARE @DateTest bit SET @DateTest = 'True' IF (@BirthDate = '00/00/00' OR @BirthDate = '' OR @BirthDate = '?' OR @BirthDate IS NULL) SET @ReturnValue = '1800-01-01' ELSE BEGIN IF LEN(@BirthDate) = 10 SET @ReturnValue = SUBSTRING(@BirthDate, 7, 4) + '-' + SUBSTRING(@BirthDate, 4, 2) + '-' + LEFT(@BirthDate, 2) ELSE SET @ReturnValue = '19' + SUBSTRING(@BirthDate, 7, 2) + '-' + SUBSTRING(@BirthDate, 4, 2) + '-' + LEFT(@BirthDate, 2) END IF (SUBSTRING(@ReturnValue, 1, 2) <> '19' AND SUBSTRING(@ReturnValue, 1, 2) <> '20') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 3, 1) < '0' OR SUBSTRING(@ReturnValue, 3, 2) > '9') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 4, 1) < '0' OR SUBSTRING(@ReturnValue, 4, 2) > '9') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 6, 1) < '0' OR SUBSTRING(@ReturnValue, 6, 2) > '1') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 7, 1) < '0' OR SUBSTRING(@ReturnValue, 7, 2) > '9') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 9, 1) < '0' OR SUBSTRING(@ReturnValue, 9, 2) > '3') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 10, 1) < '0' OR SUBSTRING(@ReturnValue, 10, 2) > '9') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 5, 1) <> '-') SET @DateTest = 'False' IF (SUBSTRING(@ReturnValue, 8, 1) <> '-') SET @DateTest = 'False' IF @DateTest = 'False' SET @ReturnValue = '1800-01-01' RETURN @ReturnValue END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO