Date Validations?

  • How To Validate a year, month,and date and totaldate?Year should be in yyyy format,month should be between

    1 and 1 to 12 and date should be between 1 to 31 and total date should be YYYY-MM-DD Formate?

    Plase guys share your ideas?

  • Can you provide more information about the context please Ananth? For instance, are you designing a validation process for an import of a text-typed date?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • If you want to validate text-strings in a table field, you can use a TALLY table to match the values against.

    Create a table with a datetime field and fill it with all dates within the required range. Join this tally table with the table that holds the text-string values. Convert the datetime value of the tallytable to seperate values for year, month and day (P.S.: these values could also be added as extra columns in the tally-table). Match these year, month and day values to substrings of the text value.

    create table tally_date (id int identity (1,1)

    , date_value date)

    declare @int int

    set @int = 0

    while @int < 366

    begin

    insert into tally_date

    select dateadd(day, @int, '20120101')

    set @int = @int + 1

    end

    select * from tally_date

    create table validate_dates (date_value nvarchar(10))

    insert into validate_dates

    select '2012-02-00'

    union allselect '2012-02-10'

    union allselect '2012-02-29'

    union allselect '2012-03-10'

    union allselect '2012-03-21'

    union allselect '2012-21-04'

    -- join the textstring values with the tally-table and list all mismatches (i.e. invalid dates) first

    select

    vd.date_value

    , left(vd.date_value, 4) as year_value

    , substring(vd.date_value, 6, 2) as month_value

    , right(vd.date_value, 2) as day_value

    , td.date_value

    from validate_dates vd

    left outer join tally_date td

    on left(vd.date_value, 4) = year(td.date_value)

    and substring(vd.date_value, 6, 2) = month(td.date_value)

    and right(vd.date_value, 2) = day(td.date_value)

    order by

    td.date_value

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • You have a date stored as varchar or similar and you want to check it's a valid date?

    Two options - the first being vastly more preferable:

    (1) Don't store dates as varchar. Use one of the date data types.

    (2) Use CONVERT to convert it to date. If it fails, you don't have a valid date. Use the TRY...CATCH syntax to trap errors.

    John

  • John Mitchell-245523 (7/22/2013)


    You have a date stored as varchar or similar and you want to check it's a valid date?

    Two options - the first being vastly more preferable:

    (1) Don't store dates as varchar. Use one of the date data types.

    (2) Use CONVERT to convert it to date. If it fails, you don't have a valid date. Use the TRY...CATCH syntax to trap errors.

    John

    ISDATE() is useful for this too.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Ananth@Sql (7/22/2013)


    How To Validate a year, month,and date and totaldate?Year should be in yyyy format,month should be between

    1 and 1 to 12 and date should be between 1 to 31 and total date should be YYYY-MM-DD Formate?

    Plase guys share your ideas?

    What is an input for proposed validation?

    If it's one single character value, your best way would be just using ISDATE function.

    And about format. So, you want to be sure that if the date string provided, it is only in ISO format?

    (Please not that YYYYMMDD is also standard ISO format).

    I guess you can do some thing like that:

    DECLARE @validIsoDate BIT = 0

    IF ISDATE(@Value) = 1

    BEGIN

    IF CAST(YEAR(@Value) AS CHAR(4)) = LEFT(@Value,4) SET @validIsoDate =1

    END

    SELECT @validIsoDate

    The best option would be the above written as ITV function...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Here is one of the short form which can be used for ITV function

    RETURN SELECT ISNULL(CASE WHEN ISDATE(@value) = 1 THEN CASE WHEN CAST(YEAR(@Value) AS CHAR(4)) = LEFT(@Value,4) THEN 1 END END,0) AS ValidIsoDate;

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • The CASE expression will verify that the data string is in YYYY-MM-DD format, and is a valid date. It will return a 1 if the date is valid and a 0 if it isn't.

    select

    a.DT,

    IsDateValid =

    case

    when a.DT is null

    then 0

    when a.DT not like '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'

    then 0

    when isdate(replace(a.DT,'-','')) <> 1

    then 0

    else 1

    end

    from

    (

    -- Test Date

    select DT = '1752-12-31'union all

    select DT = '1753-01-01'union all

    select DT = '1900-02-28'union all

    select DT = '1900-02-29'union all

    select DT = '2000-02-29'union all

    select DT = '2004 01-01'union all

    select DT = '2004-01 01'union all

    select DT = '2013-02-29'union all

    select DT = '2013-06-30'union all

    select DT = '2013-06-31'--union all

    ) a

    order by

    a.DT

    Results:

    DT IsDateValid

    ---------- -----------

    1752-12-31 0

    1753-01-01 1

    1900-02-28 1

    1900-02-29 0

    2000-02-29 1

    2004 01-01 0

    2004-01 01 0

    2013-02-29 0

    2013-06-30 1

    2013-06-31 0

  • ISDATE may give different results depending on the datetime datatype (DATE vs DATETIME vs DATETIME2, etc.). The best and surest method to validate a date-like string as a date is to attempt to convert it to the desired datetime datatype. If it's out of scope or an invalid date the conversion will fail. In this case I'm testing by attempting a conversion to SMALLDATETIME. That value should be changed accordingly as necessary.

    Here's a procedure that will do the date validation (it will also handle UMC dates if you include the second nested procedure).

    CREATE PROCEDURE [dbo].[IsValidDate]

    @sDate VARCHAR(50)

    ,@sDateFormat CHAR(3) = 'YMD' -- MDY, DMY, YMD, YDM, MYD, DYM

    AS

    BEGIN

    /*

    EXAMPLES:

    EXEC dbo.IsValidDate '01-07-2001' -- Valid date

    EXEC dbo.IsValidDate '1/7/2001' -- Valid date

    EXEC dbo.IsValidDate '07-01-2001' -- Valid date

    EXEC dbo.IsValidDate '7/1/2001' -- Valid date

    EXEC dbo.IsValidDate '29-12-2013','DMY' -- Valid date in DMY format

    EXEC dbo.IsValidDate '29-12-2013','MDY' -- Invalid date in MDY format

    EXEC dbo.IsValidDate '32-12-2013' -- ERROR: Date out of range

    EXEC dbo.IsValidDate '29-02-2013' -- ERROR: Not a leap year

    EXEC dbo.IsValidDate 'xyz' -- ERROR: Invalid date

    EXEC dbo.IsValidDate '1234' -- Invalid std date/valid umc date

    EXEC dbo.IsValidDate '2013-01-08 15:44:12' -- Valid date

    EXEC dbo.IsValidDate '2013-01-08 15:44:12.000' -- Valid date

    EXEC dbo.IsValidDate '2013-01-08 15:44:12.208' -- Valid date

    EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606' -- Invalid std date/valid umc date

    EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30' -- Invalid std date/valid umc date

    */

    SET NOCOUNT ON

    SET DATEFORMAT @sDateFormat

    DECLARE

    @dStdDate SMALLDATETIME

    ,@dUMCDate DATETIMEOFFSET

    ,@bIsValidUMCDate BIT

    ,@bIsValidDate BIT

    SET @dStdDate = NULL

    SET @dUMCDate = NULL

    /* Check to see if this is a valid UMC date */

    IF OBJECT_ID('tempdb..#CheckUMCDate') IS NOT NULL

    DROP TABLE #CheckUMCDate

    CREATE TABLE #CheckUMCDate (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [InputDate] VARCHAR(50) NULL,

    [ConvertedDate] VARCHAR(50) NULL,

    [IsValidUMCDate] BIT NULL

    PRIMARY KEY (ID))

    BEGIN TRY

    INSERT INTO #CheckUMCDate

    EXEC dbo.IsValidUMCDate @sDate

    END TRY

    BEGIN CATCH

    INSERT INTO #CheckUMCDate

    SELECT

    @sDate AS InputDate

    ,NULL ConvertedDate

    ,0 AS IsValidUMCDate

    END CATCH

    SELECT

    @dUMCDate = ConvertedDate

    ,@bIsValidUMCDate = IsValidUMCDate

    FROM

    #CheckUMCDate

    /* Check date by converting into other date datatypes. */

    /* The date datatypes to use can (should) be changed */

    /* depending on your requirements. Different date */

    /* datatypes will give different results! */

    BEGIN TRY

    SET @dStdDate = CONVERT(SMALLDATETIME,@sDate)

    SET @bIsValidDate = 1

    END TRY

    BEGIN CATCH

    SET @bIsValidDate = 0

    SET @dStdDate = NULL

    END CATCH

    SELECT

    @sDate AS InputDate

    ,@dStdDate AS StdDate

    ,@bIsValidDate AS IsValidDate

    ,@dUMCDate AS UMCDate

    ,@bIsValidUMCDate AS IsValidUMCDate

    END

    GO

    CREATE PROCEDURE [dbo].[IsValidUMCDate]

    @sDate VARCHAR(50)

    ,@sDateFormat CHAR(3) = 'YMD' -- MDY, DMY, YMD, YDM, MYD, DYM

    AS

    BEGIN

    SET NOCOUNT ON

    SET DATEFORMAT @sDateFormat

    DECLARE

    @dUMCDate DATETIMEOFFSET

    ,@bIsValidUMCDate BIT

    ,@ERROR BIT

    SET @dUMCDate = NULL

    BEGIN TRY

    SET @dUMCDate = CONVERT(DATETIMEOFFSET,@sDate)

    SET @bIsValidUMCDate = 1

    END TRY

    BEGIN CATCH

    SET @bIsValidUMCDate = 0

    SET @dUMCDate = NULL

    END CATCH

    SET @ERROR = @bIsValidUMCDate

    SELECT

    @sDate AS InputDate

    ,@dUMCDate AS ConvertedDate

    ,@ERROR AS IsValidUMCDate

    END

    GO

     

  • Michael Valentine Jones (7/22/2013)The CASE expression will verify that the data string is in YYYY-MM-DD format, and is a valid date. It will return a 1 if the date is valid and a 0 if it isn't.

    select

    a.DT,

    IsDateValid =

    case

    when a.DT is null

    then 0

    when a.DT not like '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'

    then 0

    when isdate(replace(a.DT,'-','')) <> 1

    then 0

    else 1

    end

    ...

    +1

    This should be the easiest way and 100% accurate, since 'YYYYMMDD' is always correctly interpreted.

    [As a very minor aside, I prefer the syntax:

    '[0-9][0-9][0-9][0-9][-][0-1][0-9][-][0-3][0-9]'

    because I believe it's a shade clearer, making it explicitly clear that the dashes chars and not range indicators.]

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • --this date MAY or MAY NOT be valid depending on the DATEFORMAT setting

    SELECT ISDATE('29-12-2013')

    --this is never valid (out-of-range)

    SELECT ISDATE('32-12-2013')

    --this should always be valid in all formats

    SELECT ISDATE('2013-01-08 15:44:12.208')

    --this is a valid UMC date but ISDATE = 0

    SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30')

    DECLARE @testdate VARCHAR(20)

    SET @testdate = '29-12-2013' -- this is valid in DMY format

    SET @testdate = '32-12-2013' -- this is never valid (out-of-range)

    SET @testdate = '2013-01-08 15:44:12.208' -- this is valid

    SET @testdate = '2013-01-08 15:44:12.2081606 +05:30' -- this is valid UMC date

    --for these testdates using the script below,

    --#1 is ambiguous, #2 is correct, #3 & #4 are INCORRECT

    SELECT

    @testdate

    ,IsDateValid = CASE WHEN @testdate IS NULL THEN 0

    WHEN @testdate NOT LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' THEN 0

    WHEN ISDATE(REPLACE(@testdate,'-','')) <> 1 THEN 0

    ELSE 1

    END

    --this procedure returns the proper results

    EXEC dbo.IsValidDate '29-12-2013','DMY'

    EXEC dbo.IsValidDate '32-12-2013'

    EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'

    EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'

  • Steven Willis (7/22/2013)


    --this date MAY or MAY NOT be valid depending on the DATEFORMAT setting

    SELECT ISDATE('29-12-2013')

    --this is never valid (out-of-range)

    SELECT ISDATE('32-12-2013')

    --this should always be valid in all formats

    SELECT ISDATE('2013-01-08 15:44:12.208')

    --this is a valid UMC date but ISDATE = 0

    SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30')

    DECLARE @testdate VARCHAR(20)

    SET @testdate = '29-12-2013' -- this is valid in DMY format

    SET @testdate = '32-12-2013' -- this is never valid (out-of-range)

    SET @testdate = '2013-01-08 15:44:12.208' -- this is valid

    SET @testdate = '2013-01-08 15:44:12.2081606 +05:30' -- this is valid UMC date

    --for these testdates using the script below,

    --#1 is ambiguous, #2 is correct, #3 & #4 are INCORRECT

    SELECT

    @testdate

    ,IsDateValid = CASE WHEN @testdate IS NULL THEN 0

    WHEN @testdate NOT LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' THEN 0

    WHEN ISDATE(REPLACE(@testdate,'-','')) <> 1 THEN 0

    ELSE 1

    END

    --this procedure returns the proper results

    EXEC dbo.IsValidDate '29-12-2013','DMY'

    EXEC dbo.IsValidDate '32-12-2013'

    EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'

    EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'

    The OP stated that the total date should be in format YYYY-MM-DD, so the test I gave them looks for exactly that, and is insensitive to the setting of DATEFORMAT.

    From OP:

    "Year should be in yyyy format ,month should be between 1 and 1 to 12 and date should be between 1 to 31 and total date should be YYYY-MM-DD Formate?"

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply