locked
Date Format RRS feed

  • Question

  • Hi, I have table with string data type column . in that column i have difrent types of dates is there. i want check is coming record is mm/dd/yyyyy format or another format.

    if record has mm/dd/yyyy format means correct record, reamaing all all other formats has invalid records...

    want to check record is valid or not valid record.

    how can i write the select query.

    Tuesday, June 5, 2012 11:36 AM

Answers

  • How do you know whether 06/05/2012 is the correct format or not?

    Always store date and time values in the data types that are designed from them.

    You can use the isdate() function to find dates that SQL Server cannot convert. To test for this specifc format you can do:

    ; WITH twisted AS (
        SELECT replace(datecol, '/', '.') AS twisted
        FROM   tbl
    )
    SELECT *
    FROM   twisted
    WHERE  isdate(parsename(twisted, 1) + parsename(twisted, 3) +
                  parsename(twisted, 2)) = 0

    However, if 06/05/2012 was entered to mean 6 May 2012, you lose.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Hasham NiazEditor Tuesday, June 5, 2012 12:43 PM
    • Marked as answer by Iric Wen Wednesday, June 13, 2012 9:49 AM
    Tuesday, June 5, 2012 12:09 PM
  • how can we handle the null values.....

    If any records having null value .. thats a valid record..

    The query I gave you lists the rows with invalid dates. It will not list
    NULL values. Keep in mind that NULL compared to anything else returns
    UNKNOWN, and will not pass the filter in the WHERE clause.

    If you want to filter out the valid dates, including the NULL values, you
    need:

    ; WITH twisted AS (
        SELECT replace(datecol, '/', '.') AS twisted
        FROM   tbl
    )
    SELECT *
    FROM   twisted
    WHERE  isdate(parsename(twisted, 1) + parsename(twisted, 3) +
                  parsename(twisted, 2)) = 1
       OR  twisted IS NULL


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Wednesday, June 13, 2012 9:49 AM
    Tuesday, June 5, 2012 12:42 PM

All replies

  • SET DATEFORMAT dmy;
    SELECT ISDATE('15/04/2008'); --Returns 1.
    If the date is correct it returns 1, else it return 0.
    • Edited by irusul Tuesday, June 5, 2012 11:47 AM
    Tuesday, June 5, 2012 11:39 AM
  • One way to implement it as below:

    DECLARE @a VARCHAR(12)
    SET @a = '21/21/2012'
    SELECT  CASE WHEN SUBSTRING(@a, 1, 2) BETWEEN 1 AND 12
                 THEN CASE WHEN SUBSTRING(@a, 4, 2) BETWEEN 1 AND 31
                           THEN CASE WHEN SUBSTRING(@a, 7, 4) BETWEEN 1999 AND 9999
                                     THEN 1
                                     ELSE 0
                                END
                           ELSE 0
                      END
                 ELSE 0
            END
    Above code return 1 if proper date is supplied else it will return 0.
    Tuesday, June 5, 2012 11:43 AM
  • Similar to what Hardik stated:

    declare @test table( string_Date varchar(10) );
    insert into @test
    select null union all select '02/30/2012' union all
    select '02/29/2012' union all select '29/02/2012' union all
    select '2012/02/29' union all select '12/31/9999'
    ;
    select
      string_Date,
      case when isDate(string_Date) = 1
            and string_Date like '[0-1][0-9]/[0-3][0-9]/[1-9][0-9][0-9][0-9]'
    		  then 'Valid' else 'Invalid'
      end as validation
    from @test;
    /* -------- Output: --------
    string_Date validation
    ----------- ----------
    NULL        Invalid
    02/30/2012  Invalid
    02/29/2012  Valid
    29/02/2012  Invalid
    2012/02/29  Invalid
    12/31/9999  Valid
    */


    Tuesday, June 5, 2012 12:07 PM
  • How do you know whether 06/05/2012 is the correct format or not?

    Always store date and time values in the data types that are designed from them.

    You can use the isdate() function to find dates that SQL Server cannot convert. To test for this specifc format you can do:

    ; WITH twisted AS (
        SELECT replace(datecol, '/', '.') AS twisted
        FROM   tbl
    )
    SELECT *
    FROM   twisted
    WHERE  isdate(parsename(twisted, 1) + parsename(twisted, 3) +
                  parsename(twisted, 2)) = 0

    However, if 06/05/2012 was entered to mean 6 May 2012, you lose.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Hasham NiazEditor Tuesday, June 5, 2012 12:43 PM
    • Marked as answer by Iric Wen Wednesday, June 13, 2012 9:49 AM
    Tuesday, June 5, 2012 12:09 PM
  • try this :

    declare @str varchar(100)
    declare @str1 varchar(100)
    
    select @str = '10/12/2012',
    		@str1 = '13/10/2012'
    
    
    select 
    		case 
    			when ISDATE(@str) = 1 THEN 
    				case 
    					when @str = CONVERT(varchar(10),cast(@str as datetime),101) THEN @str
    					else 'date but not in correct format'
    				end
    			else 'not in date format'
    		end as str,
    		case 
    			when ISDATE(@str1) = 1 THEN 
    				case 
    					when @str1 = CONVERT(varchar(10),cast(@str1 as datetime),101) THEN @str1
    					else 'date but not in correct format'
    				end
    			else 'not in date format'
    		end as str1
    				
    
    


    Please vote if you find this posting was helpful or Mark it as answered.

    Tuesday, June 5, 2012 12:14 PM
  • hhi,

    Hi Erland ..

    how can we handle the null values.....

    If any records having null value .. thats a valid record..

    Tuesday, June 5, 2012 12:35 PM
  • how can we handle the null values.....

    If any records having null value .. thats a valid record..

    The query I gave you lists the rows with invalid dates. It will not list
    NULL values. Keep in mind that NULL compared to anything else returns
    UNKNOWN, and will not pass the filter in the WHERE clause.

    If you want to filter out the valid dates, including the NULL values, you
    need:

    ; WITH twisted AS (
        SELECT replace(datecol, '/', '.') AS twisted
        FROM   tbl
    )
    SELECT *
    FROM   twisted
    WHERE  isdate(parsename(twisted, 1) + parsename(twisted, 3) +
                  parsename(twisted, 2)) = 1
       OR  twisted IS NULL


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Wednesday, June 13, 2012 9:49 AM
    Tuesday, June 5, 2012 12:42 PM
  • In SQL 2012 you may want to use TRY_CONVERT function to check if the date can be converted using the appropriate style.

    Check http://msdn.microsoft.com/en-us/library/hh230993.aspx

    So,

    case when DateCol IS NULL then 'Valid' when TRY_CONVERT(date, DateCol,  101) IS NULL then 'Invalid' else 'Valid' end as TestDate


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, June 5, 2012 1:21 PM
  • Hi,

    Normally you cant find out if a date is in DD/MM/YYYY or MM/DD/YYYY in the database

    Take a simple example 01/02/2012. is this date 2nd Jan or 1st Feb? There is no way you can distinguish this unless you use a common format to store all the dates. If you have a mix of formats then, it is a problem.

    As some earlier post mentioned you can just check whether the value is a valid date or not using ISDATE() Functions.

    Hope this helps

    Regards
    satheesh



    Tuesday, June 5, 2012 1:57 PM